Page 1 of 1

Merging records

PostPosted: Fri Apr 20, 2012 1:19 pm
by Puffe
Hi,

I want to merge records from different records with the same keyvalue and I wonder hos this can be done.

My file is fixed blocked with recordlength 11 and the key i position 1-4 and a numeric value in position 5-11.
The file contains following records:
2009 4520
2010 4701
2011 6613
2009 416
2010 388
2011 614
2009 257
2010 235
2011 260

I want all records that have key 2009 to look like this (from the three records)
2009 4520 416 257
and the same for 2010 and 2011
2010 4701 388 235
2011 6613 614 260

Mike

Re: Merging records

PostPosted: Fri Apr 20, 2012 3:48 pm
by BillyBoyo
I saw something a bit similar the other day, so I have adapted the SPLICE solution from that.

I "sorted" your input, which would need to be done, and can be included in this step. I don't know if ICETOOL is OK for you.

//S1 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//OUT      DD SYSOUT=*
//TOOLIN   DD *
  SPLICE FROM(IN) TO(OUT) ON(1,4,CH) WITHANY USING(CTL1) -
  WITH(13,7) WITH(21,7) WITH(29,7)
//*
//CTL1CNTL DD *
  OPTION COPY
  INREC IFOUTLEN=36,
  IFTHEN=(WHEN=INIT,OVERLAY=(37:SEQNUM,2,ZD,RESTART=(1,4))),
  IFTHEN=(WHEN=(37,2,ZD,EQ,01),OVERLAY=(13:5,7)),
  IFTHEN=(WHEN=(37,2,ZD,EQ,02),OVERLAY=(21:5,7)),
  IFTHEN=(WHEN=(37,2,ZD,EQ,03),OVERLAY=(29:5,7))
  OUTFIL BUILD=(1,4,X,13,7,X,21,7,X,29,7)
//IN       DD *
2009   4520
2009    416
2009    257
2010   4701
2010    388
2010    235
2011   6613
2011    614
2011    260


Gives:

2009    4520     416     257
2010    4701     388     235
2011    6613     614     260

Re: Merging records

PostPosted: Fri Apr 20, 2012 10:11 pm
by skolusu
Puffe,

if your input always have 3 records per group , you can use the RESIZE operator to merge the records like shown below
//STEP0100 EXEC PGM=ICETOOL                     
//TOOLMSG  DD SYSOUT=*                         
//DFSMSG   DD SYSOUT=*                         
//IN       DD *                                 
----+----1----+----2----+----3----+----4----+---
2009 4520                                       
2010 4701                                       
2011 6613                                       
2009 416                                       
2010 388                                       
2011 614                                       
2009 257                                       
2010 235                                       
2011 260                                       
//OUT      DD SYSOUT=*                         
//TOOLIN   DD *                                 
  RESIZE FROM(IN) TO(OUT) TOLEN(33) USING(CTL1)
//CTL1CNTL DD *                                 
  SORT FIELDS=(1,4,CH,A),EQUALS                 
  INREC BUILD=(1,11)                           
  OUTFIL BUILD=(1,11,16,7,27,7)                 
//*


The output from the above is
2009 4520   416    257
2010 4701   388    235
2011 6613   614    260