Page 1 of 1

Combine all records related to each account

PostPosted: Sat Aug 07, 2010 1:13 am
by avisn
Hi,
I have multiple records for a given account id and trying to combine all records related to each
account into one single record. Number of records for each account may vary from 2 - 4. Can
someone please help to do this using syncsort?.

Input:
Fixed 80 record length file. No header record in the file but i mentioned here for just record layout explanation.
3 - input fields
Acct# Name/Address Type
(1-10) (11-25) (26-30)
111 name1 Name
111 street11 Add
111 street12 Add

222 name2 Name
222 street2 Add

333 name31 Name
333 name32 Name
333 street31 Add
333 street32 Add

Expected output:
looking for output where name & address in the respective fields and spaces for missing field
5 - output fields
Acct# Name1 Name2 Address1 Address2
(1-10) (11-25) (26-40) (41-55) (56-70)
111 name1 street11 street12

222 name2 street2

333 name31 name32 street31 street32

Thank you.

Re: combine multiple rows into one row using Syncsort

PostPosted: Sat Aug 07, 2010 2:01 am
by Alissa Margulies
Hello AVISN.

Which release of SyncSort for z/OS are you currently running?

Regards,

Re: combine multiple rows into one row using Syncsort

PostPosted: Sat Aug 07, 2010 2:48 am
by Alissa Margulies
Here is a SyncSort for z/OS job that should give you the desired results:
//SORT1 EXEC PGM=SORT                                             
//SORTIN  DD *                                                     
111111111 NAMEXXXXXXXXXX NAMEX                                     
111111111 STREET11XXXXXX ADDXX                                     
111111111 STREET12XXXXXX ADDXX                                     
222222222 NAME2XXXXXXXXX NAMEX                                     
222222222 STREET2XXXXXXX ADDXX                                     
333333333 NAME31XXXXXXXX NAMEX                                     
333333333 NAME32XXXXXXXX NAMEX                                     
333333333 STREET31XXXXXX ADDXX                                     
333333333 STREET32XXXXXX ADDXX                                     
//SORTOUT DD SYSOUT=*                                             
//SYSOUT  DD SYSOUT=*                                             
//SYSIN   DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,1,ZD,RESTART=(1,10))),
        IFTHEN=(WHEN=(81,1,ZD,EQ,1),BUILD=(1,10,11,15,45Z)),       
        IFTHEN=(WHEN=(81,1,ZD,EQ,2),BUILD=(1,10,15Z,11,15,30Z)),   
        IFTHEN=(WHEN=(81,1,ZD,EQ,3),BUILD=(1,10,30Z,11,15,15Z)),   
        IFTHEN=(WHEN=(81,1,ZD,EQ,4),BUILD=(1,10,45Z,11,15))         
  SORT FIELDS=(1,10,CH,A),EQUALS                                   
  SUM FIELDS=(11,8,19,8,27,8,35,8,43,8,51,8,59,8,67,4),FORMAT=BI   
/*                                     

This is the output produced from the sort step above:
111111111 NAMEXXXXXXXXXX STREET11XXXXXX STREET12XXXXXX               
222222222 NAME2XXXXXXXXX STREET2XXXXXXX                             
333333333 NAME31XXXXXXXX NAME32XXXXXXXX STREET31XXXXXX STREET32XXXXXX

Regards,

Re: combine multiple rows into one row using Syncsort

PostPosted: Mon Aug 09, 2010 9:11 pm
by avisn
hi Alissa,
I'm using SYNCSORT FOR Z/OS 1.3.2.2R version...
your solution is working...Thanks so much...!!!

Thanks

Re: combine multiple rows into one row using Syncsort

PostPosted: Sun Sep 26, 2010 12:17 am
by sdstyer
Wow! That is swift. I would not have thought of using the sum and binary zeros to merge the records into one row. That is one thing that I thought that Syncsort could not do. I have always used Fileaid to do this function, but it is much slower than syncsort.

My company has v1.3. I saw another post with WHEN=Group and PUSH commands. I wonder if that is doing the same function as this.

Thanks for showing me something new!