IBM Mainframe Forum

by **avisn** » Sat Aug 07, 2010 1:13 am

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.

by **Alissa Margulies** » Sat Aug 07, 2010 2:01 am

Hello AVISN.

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

Regards,

by **Alissa Margulies** » Sat Aug 07, 2010 2:48 am

Here is a SyncSort for z/OS job that should give you the desired results:

Select all

//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:

Select all

111111111 NAMEXXXXXXXXXX STREET11XXXXXX STREET12XXXXXX
222222222 NAME2XXXXXXXXX STREET2XXXXXXX
333333333 NAME31XXXXXXXX NAME32XXXXXXXX STREET31XXXXXX STREET32XXXXXX

Regards,

by **avisn** » Mon Aug 09, 2010 9:11 pm

hi Alissa,
I'm using SYNCSORT FOR Z/OS 1.3.2.2R version...
your solution is working...Thanks so much...!!!

Thanks

by **sdstyer** » Sun Sep 26, 2010 12:17 am

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!

IBM Mainframe Forum

Combine all records related to each account

Combine all records related to each account

Re: combine multiple rows into one row using Syncsort

Re: combine multiple rows into one row using Syncsort

Re: combine multiple rows into one row using Syncsort

Re: combine multiple rows into one row using Syncsort