Page 1 of 1

Join the files and based on condition need to sumup

PostPosted: Thu May 05, 2011 5:02 pm
by padmaja_nan
Please see the example input files below
Input File 1:

COl1
AAA
BBB
CCC


Output file 2:
Col1 col2 col3
AAA Cond1 1234
AAA Cond1 4567
AAA cond2 7890
AAA Cond3 9999
AAA cond2 6666
AAA cond3 1111
BBB cond1 7777
BBB cond1 8888
BBB cond3 9999
BBB cond3 1000
CCC cond1 1111
CCC cond2 4444

Required output is :

AAA cond1 sum of (1234+4567 )
AAA cond2 sum of (7890 + 6666)
AAA cond3 sum of (9999 + 1111)
BBB ....................
CCC..............

My requirement is if the the first column of the the first file matches with the first column of the second file ,then all the all the values of third numeric field should be summed up for matching second column values .

Please help me .

Re: Join the files and based on condition need to sumup

PostPosted: Thu May 05, 2011 9:25 pm
by skolusu
padmaja_nan,

You haven't provided the LRECL and RECFM of the input files or output files. Also You show a 4 byte zd value when summed up would result in more than 4 bytes. what is the format of the numeric field to summed up?

I assumed that both input files to be FB recfm and 80 bytes lrecl. The key to be matched is 1st 3 bytes and composite key for summing is the first 10 bytes. The numeric field to be summed is 4 bytes in zd format in pos 11.

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//INA      DD *                                             
AAA                                                         
BBB                                                         
CCC                                                         
//INB      DD *                                             
AAA COND1 1234                                             
AAA COND1 4567                                             
AAA COND2 7890                                             
AAA COND3 9999                                             
AAA COND2 6666                                             
AAA COND3 1111                                             
BBB COND1 7777                                             
BBB COND1 8888                                             
BBB COND3 9999                                             
BBB COND3 1000                                             
CCC COND1 1111                                             
CCC COND2 4444                                             
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                             
  JOINKEYS F1=INA,FIELDS=(1,3,A)                           
  JOINKEYS F2=INB,FIELDS=(1,3,A)                           
  REFORMAT FIELDS=(F2:1,80)                                 
  SORT FIELDS=(1,10,CH,A)                                   
  OUTFIL REMOVECC,NODETAIL,                                 
  SECTIONS=(1,10,TRAILER3=(1,10,TOT=(11,4,ZD,M10,LENGTH=6)))
//*


The output from this job is

AAA COND1   5801
AAA COND2  14556
AAA COND3  11110
BBB COND1  16665
BBB COND3  10999
CCC COND1   1111
CCC COND2   4444

Re: Join the files and based on condition need to sumup

PostPosted: Fri May 06, 2011 11:50 am
by padmaja_nan
Thanks a lot Kolusu for your quick reply .
The numeric field that needs to be summed up on is S9(13)V9(2) which means it is a PD field of lenght 8 .
SO please let me know whether the below Sections line in sort card is correct fro summing up the S9(13)V9(2) field.

SECTIONS=(1,10,TRAILER3=(1,10,TOT=(11,8,PD,M16,LENGTH=16)))

And also could you please explain what is M10 and lenght in the sort card sections line .

Regards,
Padmaja

Re: Join the files and based on condition need to sumup

PostPosted: Fri May 06, 2011 3:56 pm
by NicC
please explain what is M10 and lenght in the sort card sections line


What is it about these that you do not understand from reading the manual.

Re: Join the files and based on condition need to sumup

PostPosted: Fri May 06, 2011 4:15 pm
by padmaja_nan
Hi Nicc,

For converting PD to ZD ,generally i use EDIT clause in sort card ,I would like to know about the usage of M10 or M16 ,to connvert PD to ZD and the length clause .
Could you please help me in understanding it

Re: Join the files and based on condition need to sumup

PostPosted: Fri May 06, 2011 4:35 pm
by NicC
Could you please help me in understanding it

Nope - I would have to read the manual and perhaps play with test cases neither of which I have time for.

Re: Join the files and based on condition need to sumup

PostPosted: Fri May 06, 2011 9:19 pm
by skolusu
padmaja_nan wrote:The numeric field that needs to be summed up on is S9(13)V9(2) which means it is a PD field of lenght 8 .
SO please let me know whether the below Sections line in sort card is correct fro summing up the S9(13)V9(2) field.

SECTIONS=(1,10,TRAILER3=(1,10,TOT=(11,8,PD,M16,LENGTH=16)))
And also could you please explain what is M10 and lenght in the sort card sections line .


If your numeric field starts at pos 11 then your changes are correct. M0 thru M26 are edit mask patterns. Check this link to know about the Edit Mask Patterns

The LENGTH operand is used to specify the length of the edited output field.