|
View previous topic :: View next topic
|
| Author |
Message |
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
Hi Friends!
I am new to the forum and need whatever help I can get to find a solution to a problem I am facing.
I have a file which I created using SORT as a comma delimited csv for reporting.
Input File looks like below, first I have a header and then the detailed records corresponding to it follows
| Code: |
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
|
I want to right the summary of the above records right after the detailed one for each header. Point to note here is last 3 columns in the record act as a key based on which I have to write the summary records (Write only Unique). example as below:
| Code: |
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
YES NO TOTAL
XYX 2 1 3
BB 1 0 1
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
YES NO TOTAL
SA 1 0 1
XV 1 1 2 |
I used ICETOOL to write the summary records in a separate output file, but I need to write both detailed and summary records in same file.
Request to help/guide me please.
Appreciate in advance. |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2283
Location: USA
|
|
|
|
What did you try so far?
Where are the samples of YOUR OWN code? |
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
this is what I had tried
| Code: |
//STEP1 EXEC PGM=ICETOOL
//IN DD DSN=FILE1,DISP=SHR
//OUT DD DSN=FILE2,DISP=(,CATLG,DELETE)
//TOOLIN DD *
SPLICE FROM(IN) TO(OUT) ON(1,6) WITHANY KEEPNODUPS -
WITH(1,6) WITH(8,3) WITH(13,3) USING(CTL1)
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=(25,3,CH,EQ,C'YES'),
BUILD=(1:14,6,8:21,3)),
IFTHEN=(WHEN=(25,3,CH,EQ,C'NO'),
BUILD=(1:14,6,13:21,3))
/*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=* |
just to add a note here, till now I am not writing the header with (Yes/No and its Total with value). Once I have the records written to same file as detailed, I should be able to address that.
Kindly let me know if any further details are needed. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2283
Location: USA
|
|
|
|
AFAIU the number of possible keys like XYX is not fixed within the same key like ABCD?
If so, you may need two-three stages of ICETOOL, or two-three job steps of DFSORT.
First, create an intermediate sums table (or "file") like this one:
| Code: |
ABCD, ,XYX , ,YES 2
ABCD, ,XYX , ,NO 1
ABCD, ,XYX , , 3
ABCD, ,BB , ,YES 1
ABCD, ,BB , , 1
EFGH, ,SA , ,YES 1
EFGH, ,SA , , 1
EFGH, ,XV , ,YES 1
EFGH, ,XV , ,NO 1
EFGH, ,XV , , 2
|
Next, merge this intermediate table with your original dataset, to produce the report you need.
I personally would recommend not to use combined ICETOOL operations like SPLICE, but instead use basic DFSORT operations. This approach would allow you to better understand how the process really works, and why. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
I am able to get the following output with two DFSORT steps.
| Code: |
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
YES NO TOTAL
BB 1 0 1
XYX 4 1 5
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
YES NO TOTAL
SA 1 0 1
XV 1 1 2 |
|
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
| sergeyken wrote: |
AFAIU the number of possible keys like XYX is not fixed within the same key like ABCD?
|
Hi Sergeyken,
Appreciate your timely response
yes that's correct, possible keys for ABCD or others are not fixed. Some might have only 1, while some might have hundreds.
My initial report had only 3 columns - first, second and fifth i.e. ABCD,XYX1234,YES
then later was asked to add a column which will reflect the alphabets in 2nd column and how many times we have those Alphabets under Yes or No, 4th column denotes that like XYX with YES happens 2 times, and with NO only once, which I was able to do. now the latest additional requirement is to print the counters as a summary below the key ABCD.
I will try to generate the table with totals, but what I didn't understand is how we will be able to write only 1 record for XYX or BB etc horizontally. I am not much aware of how merge works, will do some reading and test it.
if you can guide me that will really help, from my end I will try it too.
Thank You once again! |
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
| Joerg.Findeisen wrote: |
I am able to get the following output with two DFSORT steps.
| Code: |
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
YES NO TOTAL
BB 1 0 1
XYX 4 1 5
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
YES NO TOTAL
SA 1 0 1
XV 1 1 2 |
|
Thanks Joerg. This is exactly what I need my output to look like with just one correction the counter for XYX under YES should be same as in the column which is 2.
Could you please guide me how you got it. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
This is what I have used to achieve the requirement. There may be better solutions than mine after tweaking the code.
| Code: |
//WHATEVER EXEC PGM=ICEMAN
//SORTIN DD *
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
/*
//SYSOUT DD SYSOUT=*
//SORTOUT DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1))
//SYSIN DD *
OMIT COND=(5,1,CH,EQ,C':')
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,4),PUSH=(35:ID=3)),
IFTHEN=(WHEN=INIT,OVERLAY=(38:SEQNUM,2,BI,RESTART=(1,4))),
IFTHEN=(WHEN=(25,1,CH,EQ,C'Y'),
OVERLAY=(46:+1,BI,LENGTH=2,2Z,46,2)),
IFTHEN=(WHEN=NONE,
OVERLAY=(46:2Z,+1,BI,LENGTH=2,48,2))
SORT FIELDS=(1,4,CH,A,14,6,CH,A)
OUTFIL FNAMES=(SORTOUT),
REMOVECC,
IFTHEN=(WHEN=(38,2,BI,EQ,+1),
BUILD=(1,4,C':',35:35,3,2Z,/,1,34,35,5)),
SECTIONS=(1,4,14,6,
TRAILER3=(7X,C'YES',2X,C'NO',X,C'TOTAL',35:35,3,/,
14,6,X,
TOT=(46,2,BI,M10,LENGTH=3),X,
TOT=(48,2,BI,M10,LENGTH=3),X,
TOT=(50,2,BI,M10,LENGTH=5),35:35,3,14,6))
END
/*
//FINALIZE EXEC PGM=ICEMAN
//SORTIN DD DISP=(OLD,PASS),DSN=*.WHATEVER.SORTOUT
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION EQUALS
SORT FIELDS=(35,11,BI,A)
SUM FIELDS=(NONE)
OUTREC BUILD=(1,34)
END
/* |
Output:
| Code: |
****** ****************************
000001 ABCD:
000002 ABCD,XYX1234,XYX ,002,YES
000003 ABCD,XYX1226,XYX ,002,YES
000004 ABCD,XYX1226,XYX ,001,NO
000005 ABCD,BB1241 ,BB ,001,YES
000006 YES NO TOTAL
000007 BB 1 0 1
000008 XYX 2 1 3
000009 EFGH:
000010 EFGH,SA1242 ,SA ,001,YES
000011 EFGH,XV01 ,XV ,001,YES
000012 EFGH,X234V1 ,XV ,001,NO
000013 YES NO TOTAL
000014 SA 1 0 1
000015 XV 1 1 2
****** **************************** |
|
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
Thanks Joerg. much appreciated.
I will try this out, will definitely have to tweak the code, since I have to add some more data (additional records) to the report in the summary. Hope it will work.
Will keep posted.
Thank You! |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
The better the sample data, the better the output. Other members of the forum, as said, may have better solutions.
I am confident the addition of more fields will work, you need to imagine what fields are needed before coding to get the result you aim for. |
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
Thanks Joerg for the help.
My latest input data has changed slightly and now it has another summary detail as shown below:
| Code: |
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
ABCD,1234567, , ,YES
,
TOTAL YES Keys: 004 TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
EFGH,9876543, , ,NO
,
TOTAL YES Keys: 002 TOTAL NO: 002
SUM OF ALL YES/NO: 004 |
the sort card provided by you worked well until I had those 2 summary records printed in input. I even placed an OVERLAY criteria if TOTAL or SUM found in 1st position then, it should not be part of Sections, but it keeps on writing additonal line above and below Total and Sum at 1st position.
like this as shown below:
| Code: |
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
YES NO TOTAL
BB 1 0 1
XYX 2 1 3
TOTA
TOTAL YES Keys: 004 TOTAL NO: 001
YES NO TOTAL
: 00
SUM OF ALL YES/NO: 005
YES NO TOTAL
/NO |
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
Make it easy on me. Show me the new Input and the desired Output.
PS: OMIT the two new lines as well as they don't seem to be needed. |
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
Hi Joerg.
New input is as below, addition to the old one is that for the keys like ABCD, EFGH, etc we could have only numeric values in 2nd column (position 6 of 7 bytes ), which will result in blanks for columns 3 and 4. But can still hold value YES/NO.
addition of 2 lines shows the summary of the detailed records, how many Total Yes/NO present including the records having space in column 3, so we have to keep those 2 lines for each key.
Hence input will be as below:
| Code: |
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
ABCD,1234567, , ,YES
,
TOTAL YES Keys: 004 TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
EFGH,9876543, , ,NO
,
TOTAL YES Keys: 002 TOTAL NO: 002
SUM OF ALL YES/NO: 004 |
while the expected output should look like as below:
| Code: |
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
ABCD,1234567, , ,YES
,
YES NO TOTAL
BB 1 0 1
XYX 2 1 3
,
TOTAL YES Keys: 004 TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
EFGH,9876543, , ,NO
,
YES NO TOTAL
SA 1 0 1
XV 1 1 2
,
TOTAL YES Keys: 002 TOTAL NO: 002
SUM OF ALL YES/NO: 004 |
The alphabets in 3rdcolumn derived from 2nd column is important as those shows different types of terminals (for specific business purpose) which is required for reporting. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
| Code: |
//WHATEVER EXEC PGM=ICEMAN
//SORTIN DD *
ABCD:
ABCD,XYX1234,XYX ,002,YES
ABCD,XYX1226,XYX ,002,YES
ABCD,XYX1226,XYX ,001,NO
ABCD,BB1241 ,BB ,001,YES
ABCD,1234567, , ,YES
,
TOTAL YES Keys: 004 TOTAL NO: 001
SUM OF ALL YES/NO: 005
,
EFGH:
EFGH,SA1242 ,SA ,001,YES
EFGH,XV01 ,XV ,001,YES
EFGH,X234V1 ,XV ,001,NO
EFGH,9876543, , ,NO
,
TOTAL YES Keys: 002 TOTAL NO: 002
SUM OF ALL YES/NO: 004
/*
//SYSOUT DD SYSOUT=*
//SORTOUT DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1))
//DATA DD DISP=(NEW,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1))
//SYSIN DD *
INREC IFTHEN=(WHEN=GROUP,
BEGIN=(5,1,CH,EQ,C':'),PUSH=(45:ID=5,SEQ=5)),
IFTHEN=(WHEN=(25,1,CH,EQ,C'Y'),OVERLAY=(55:+1,BI,LENGTH=2,2Z)),
IFTHEN=(WHEN=(25,1,CH,EQ,C'N'),OVERLAY=(55:2Z,+1,BI,LENGTH=2)),
IFTHEN=(WHEN=(1,1,CH,EQ,C',',OR,1,6,SS,EQ,C'TOTAL ,SUM OF'),
OVERLAY=(50:SEQNUM,5,ZD,START=99900,INCR=10,RESTART=(45,5)))
SORT FIELDS=(1,4,CH,A,14,6,CH,A)
OUTFIL FNAMES=(SORTOUT),
INCLUDE=(25,1,SS,EQ,C'YN',AND,14,1,CH,NE,C' '),
REMOVECC,
IFTHEN=(WHEN=INIT,OVERLAY=(50:SEQNUM,5,ZD,START=2,RESTART=(1,4))),
SECTIONS=(1,4,14,6,
TRAILER3=(14,6,X,
TOT=(55,2,BI,M10,LENGTH=3),X,
TOT=(57,2,BI,M10,LENGTH=3),X,
COUNT=(M10,LENGTH=5),45:45,5,C'999001'))
OUTFIL FNAMES=(DATA),SAVE,
REMOVECC,
IFTHEN=(WHEN=(50,5,ZD,EQ,+99900),
BUILD=(C',',45:45,10,/,
C' YES NO TOTAL',45:45,5,+999000,ZD,LENGTH=6,/,
1,49,+999002,ZD,LENGTH=6))
END
/*
//FINALIZE EXEC PGM=ICEMAN
//SORTIN DD DISP=(OLD,PASS),DSN=*.WHATEVER.SORTOUT
// DD DISP=(OLD,PASS),DSN=*.WHATEVER.DATA
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(45,11,BI,A)
OUTREC BUILD=(1,44)
END
/* |
Output:
| Code: |
****** **************************** Datenanfang ****
000001 ABCD:
000002 ABCD,BB1241 ,BB ,001,YES
000003 ABCD,XYX1234,XYX ,002,YES
000004 ABCD,XYX1226,XYX ,002,YES
000005 ABCD,XYX1226,XYX ,001,NO
000006 ABCD,1234567, , ,YES
000007 ,
000008 YES NO TOTAL
000009 BB 1 0 1
000010 XYX 2 1 3
000011 ,
000012 TOTAL YES Keys: 004 TOTAL NO: 001
000013 SUM OF ALL YES/NO: 005
000014 ,
000015 EFGH:
000016 EFGH,SA1242 ,SA ,001,YES
000017 EFGH,XV01 ,XV ,001,YES
000018 EFGH,X234V1 ,XV ,001,NO
000019 EFGH,9876543, , ,NO
000020 ,
000021 YES NO TOTAL
000022 SA 1 0 1
000023 XV 1 1 2
000024 ,
000025 TOTAL YES Keys: 002 TOTAL NO: 002
000026 SUM OF ALL YES/NO: 004
****** **************************** Datenende ****** |
|
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
Thank You Joerg, much appreciated it.
I will try this and let you know. |
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
Hi Joerg,
It worked perfectly, thank you so very much.
I need to learn/practice more about sections, very useful. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2283
Location: USA
|
|
|
|
Instead of
| Code: |
| 1,6,SS,EQ,C'TOTAL ,SUM OF' |
might be better (for clarity, and efficiency) to code it as
| Code: |
| 1,6,CH,EQ,L(C'TOTAL',C'SUM OF') |
Using format ZD instead of BI for counters would make the debugging of code easier. (Though, sorting of intermediate results would also need appropriate updates) |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1441
Location: Bamberg, Germany
|
|
|
|
AFAIK the L(..) Syntax is not supported by DFSORT, but I may be wrong. Regarding the ZD format, this would indeed be better for debugging the code.  |
|
| Back to top |
|
|
ssa
New User
Joined: 03 Apr 2022
Posts: 10
Location: United States
|
|
|
|
| I will definitely try that just to know different ways of achieving the successful result. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
