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 

Merge the records based on the Span-date

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


Joined: 25 Apr 2008
Posts: 36
Topics: 8
Location: Baltimore, USA
PostPosted: Sat Apr 11, 2009 11:46 pm    Post subject: Merge the records based on the Span-date Reply with quote
We have input dataset with 80 byte size and it is sorted based on C1, C2, C3 as ASC and C4 as DESC. C4 is a Span Start date which has multiple Span Start date. And C5 is the span-end date. So, we have multiple records for the same C1, C2 and C3.

I want to merge those records into a single record. The multiple Span-start date and end-date will be another new column in the target file to make it a single row. Maximum 5 date span will be allowed in the merging and if more than 5 span for a particular C1,C2 & C3 then we will skip those extra 5 spans. Please see the example below.

The Input file,
Code:
C1      C2      C3      C4              C5       
101     ABC     201     12/12/2008      12/31/9999
101     ABC     201     12/12/2006      11/01/2008
101     ABC     201     06/06/1999      08/03/2000
106     XYZ     199     06/06/1999      08/03/2000
109     XYZ     201     12/12/2008      12/31/9999
109     XYZ     202     12/12/2008      12/31/9999
110     RRR     999     12/12/2008      12/31/9999
110     RRR     999     06/06/1999      06/06/2000
110     RRR     999     06/06/1998      06/06/1999
110     RRR     999     06/06/1996      06/06/1997
110     RRR     999     06/06/1995      06/09/1995
110     RRR     999     06/06/1993      06/09/1994
110     RRR     999     06/06/1991      06/06/1992
112     RRR     999     12/12/2008      12/31/9999
455     RRR     999     12/12/2008      12/31/9999
890     FFF     999     12/12/2008      12/31/9999
890     FFF     999     06/06/1991      06/06/1992


Target file has only 5 occurrences of Start-date and End-date file, if any C1, C2, C3 has more than 5 records; it should accommodate only the first 5 starting and ending spans. The output file should be
Code:
RC1   RC2   RC3   RC-ST-DT-1   RC-ED-DT-1   RC-ST-DT-2   RC-ED-DT-2   RC-ST-DT-3   RC-ED-DT-3   RC-ST-DT-4   RC-ED-DT-4   RC-ST-DT-5   RC-ED-DT-5
101   ABC   201   12/12/2008   12/31/9999   12/12/2006   11/01/2008   06/06/1999   08/03/2000            
106   XYZ   199   06/06/1999   08/03/2000                        
109   XYZ   201   12/12/2008   12/31/9999                        
109   XYZ   202   12/12/2008   12/31/9999                        
110   RRR   999   12/12/2008   12/31/9999   06/06/1999   06/06/2000   06/06/1998   06/06/1999   06/06/1996   06/06/1997   06/06/1995   06/09/1995
112   RRR   999   12/12/2008   12/31/9999                        
455   RRR   999   12/12/2008   12/31/9999                        
890   FFF   999   12/12/2008   12/31/9999   06/06/1991   06/06/1992                  

Eventually, the output file has a unique record based on RC1, RC2 & RC3 and the duplicate records from the input file's Span start date and end-date will be merged with the respective date fileds in the output file. Maximum 5 date span (start & end) date will be allowed others skipped. Thanks for your help to find the logic.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Sun Apr 12, 2009 8:04 pm    Post subject: Reply with quote
hisabarinath,


You can very easily PUSH the records using the new WHEN=GROUP function of DFSORT available with z/OS DFSORT V1R5 PTF UK90013 (July, 2008) like this:

Code:

//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                         
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
101     ABC     201     12/12/2008      12/31/9999                     
101     ABC     201     12/12/2006      11/01/2008                     
101     ABC     201     06/06/1999      08/03/2000                     
106     XYZ     199     06/06/1999      08/03/2000                     
109     XYZ     201     12/12/2008      12/31/9999                     
109     XYZ     202     12/12/2008      12/31/9999                     
110     RRR     999     12/12/2008      12/31/9999                     
110     RRR     999     06/06/1999      06/06/2000                     
110     RRR     999     06/06/1998      06/06/1999                     
110     RRR     999     06/06/1996      06/06/1997                     
110     RRR     999     06/06/1995      06/09/1995                     
110     RRR     999     06/06/1993      06/09/1994                     
110     RRR     999     06/06/1991      06/06/1992                     
112     RRR     999     12/12/2008      12/31/9999                     
455     RRR     999     12/12/2008      12/31/9999                     
890     FFF     999     12/12/2008      12/31/9999                     
890     FFF     999     06/06/1991      06/06/1992                     
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                                                         
  SORT FIELDS=COPY                                                     
  INREC IFTHEN=(WHEN=INIT,                                             
  BUILD=(1,24,134:25,10,X,41,10,X,156:SEQNUM,8,ZD,RESTART=(1,23))),     
  IFTHEN=(WHEN=GROUP,BEGIN=(156,8,ZD,EQ,1),RECORDS=5,PUSH=(025:134,22)),
  IFTHEN=(WHEN=GROUP,BEGIN=(156,8,ZD,EQ,2),RECORDS=4,PUSH=(047:134,22)),
  IFTHEN=(WHEN=GROUP,BEGIN=(156,8,ZD,EQ,3),RECORDS=3,PUSH=(069:134,22)),
  IFTHEN=(WHEN=GROUP,BEGIN=(156,8,ZD,EQ,4),RECORDS=2,PUSH=(091:134,22)),
  IFTHEN=(WHEN=GROUP,BEGIN=(156,8,ZD,EQ,5),RECORDS=1,PUSH=(113:134,22))
  OUTREC IFTHEN=(WHEN=(156,8,ZD,EQ,1),OVERLAY=(047:88X)),               
  IFTHEN=(WHEN=(156,8,ZD,EQ,2),OVERLAY=(069:66X)),                     
  IFTHEN=(WHEN=(156,8,ZD,EQ,3),OVERLAY=(091:44X)),                     
  IFTHEN=(WHEN=(156,8,ZD,EQ,4),OVERLAY=(113:11X))                       
  OUTFIL OMIT=(156,8,ZD,GT,5),REMOVECC,NODETAIL,BUILD=(133X),           
  SECTIONS=(1,23,TRAILER3=(1,133))                                     
//* 


If you don't have the July, 2008 PTF installed, ask your System Programmer to install it (it's free).

For complete details on the new WHEN=GROUP and the other new functions available with PTF UK90013, see:

www.ibm.com/systems/support/storage/software/sort/mvs/ugpf/
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
hisabarinath
Beginner


Joined: 25 Apr 2008
Posts: 36
Topics: 8
Location: Baltimore, USA
PostPosted: Mon Apr 13, 2009 12:52 am    Post subject: Reply with quote
Kolusu - You always great... Thank you somuch.
Back to top
View user's profile Send private message
aashish.paul
Beginner


Joined: 17 Apr 2008
Posts: 12
Topics: 3
PostPosted: Thu Jul 15, 2010 2:07 pm    Post subject: Reply with quote
Hi,
Is there a way that the above can be acomplished using syncsort?

Thanks.
Back to top
View user's profile Send private message
papadi
Supermod


Joined: 20 Oct 2009
Posts: 594
Topics: 1
PostPosted: Thu Jul 15, 2010 4:00 pm    Post subject: Reply with quote
If your release of Syncsort is current, i believe it will work.

Suggest you try it.
_________________
All the best,

di
Back to top
View user's profile Send private message
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