Posted: Wed Feb 22, 2017 9:41 am Post subject: Summing up input records by group
Hello,
I have a key file in input and need to count all those records with the same key without touching my input file (which should be already in order, so no need to sort it), follows an input example:
Code:
1 A
2 A
3 B
4 B
5 B
6 C
7 D
8 D
9 E
10 E
I would need to calculate how many A I have, how many B and so on, I also need to write this number on output file. I need to write this number on the first key of each block, or at least on all records with the same key.
So I would need an output like this:
Code:
1 A 2
2 A 2
3 B 3
4 B 3
5 B 3
6 C 1
7 D 2
8 D 2
9 E 2
10 E 2
First column is num_rec (sequential number), 2nd is the key I need to count, 3rd column is how many keys I have in input (the counter I need to calculate).
Writing the sum only on the last key before it changes is not good to me,
I need to process again this output and I need to know in advance how many key I have in that group. Number of records with the same key is variable, and it might be a big number.
Is it possible to calculate this in a sort or maybe I would need to code a program?
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
Posted: Wed Feb 22, 2017 11:11 am Post subject: Re: Summing up input records by group
Fab wrote:
Is it possible to calculate this in a sort or maybe I would need to code a program?
Fab,
You don't need a program to get the summary values. You can use JOINKEYS feature , but the trick here is to use the same file and match it to itself. I assumed the key is at position 5 for a length of 1 byte and you need an 8 byte count. Something like this
Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD *
1 A
2 A
3 B
4 B
5 B
6 C
7 D
8 D
9 E
10 E
//INB DD *
1 A
2 A
3 B
4 B
5 B
6 C
7 D
8 D
9 E
10 E
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(5,1,A),SORTED,NOSEQCK
JOINKEYS F2=INB,FIELDS=(1,1,A)
REFORMAT FIELDS=(F1:01,09, $ 1-9 BYTES FROM FILE1
F2:02,08, $ 8 BYTE COUNT
F1:18,63) $ REST OF THE DATA FROM FILE1
//*
//JNF2CNTL DD *
INREC BUILD=(5,1, $ KEY
C'00000001') $ 8 BYTE INIT-COUNT OF 1
SUM FIELDS=(2,8,ZD) $ SUM THE COUNT
//*
The output from this job is
Code:
1 A 00000002
2 A 00000002
3 B 00000003
4 B 00000003
5 B 00000003
6 C 00000001
7 D 00000002
8 D 00000002
9 E 00000002
10 E 00000002
Let me know if you want the leading zeros suppressed for the count
Many thanks Kolusu,
let's see if I understood what you did there, first it comes INREC which build a temporary input, with just the key I have to count and the counter itself. then you do a sum on it to count how many we have. Then you specify the keys from input, but why you wrote this?
Code:
JOINKEYS F2=INB,FIELDS=(1,1,A)
finally you do a REFORMAT to build output, and you build it to be 80 bytes, did I understood you well?
I need to study a bit your code first, so I can fully understand. But thank you very much again.
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
Posted: Wed Feb 22, 2017 12:10 pm Post subject:
Fab wrote:
Many thanks Kolusu,
let's see if I understood what you did there, first it comes INREC which build a temporary input, with just the key I have to count and the counter itself.
Correct
Fab wrote:
then you do a sum on it to count how many we have.
Correct
Fab wrote:
Then you specify the keys from input, but why you wrote this?
Code:
JOINKEYS F2=INB,FIELDS=(1,1,A)
Look at JNF2CNTL1. It is building ONLY the key and counter for File2 So with BUILD the key is now moved to position 1 followed by the summing counter.
So now the position to be matched on File2 is at position 1 instead of 5 and that is why I coded the MATCHING key is at position 1 on FIELDS statement.
Fab wrote:
finally you do a REFORMAT to build output, and you build it to be 80 bytes, did I understood you well?
Correct
Fab wrote:
I need to study a bit your code first, so I can fully understand. But thank you very much again.
If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:
It worked perfectly, many thanks Kolusu. Here in my workplace we usually just use sort with minimum parameters, just a plain sort or maybe include or omit cond. I knew that this program could do much more, but I could not imagine so much more. We usually go for cobol programs, but honestly I think that using the full potential of sort could drastically reduce the number of cobol programs really necessary. I read that documentation in my free time which is not much, as you can imagine. It is quickier post here asking for support, and for that I thank you again Kolusu.
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
Posted: Fri Feb 24, 2017 12:14 pm Post subject:
Fab wrote:
It worked perfectly, many thanks Kolusu. Here in my workplace we usually just use sort with minimum parameters, just a plain sort or maybe include or omit cond. I knew that this program could do much more, but I could not imagine so much more. We usually go for cobol programs, but honestly I think that using the full potential of sort could drastically reduce the number of cobol programs really necessary. I read that documentation in my free time which is not much, as you can imagine. It is quickier post here asking for support, and for that I thank you again Kolusu.
Fab,
We are trying to change that perception of DFSORT. The true power of an utility is when we can exploit it to its fullest potential. I have converted many customer's COBOL/Easytrieve programs to DFSORT and most of them are written in using less than 30 lines of control cards.
Feel free to post ANY question and we can try to show you a way using DFSORT no matter how complicated the requirement is. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum