MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Summing up input records by group

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Wed Feb 22, 2017 9:41 am    Post subject: Summing up input records by group Reply with quote

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?

Thanks in advance for any help you can provide.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Feb 22, 2017 11:11 am    Post subject: Re: Summing up input records by group Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Wed Feb 22, 2017 11:51 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Feb 22, 2017 12:10 pm    Post subject: Reply with quote

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:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000080
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Fri Feb 24, 2017 10:48 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Fri Feb 24, 2017 12:14 pm    Post subject: Reply with quote

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. Smile
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group