IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to summarize data by grouping on a key


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
santhosh5983

New User


Joined: 11 Mar 2015
Posts: 10
Location: India

PostPosted: Tue Sep 04, 2018 9:07 am
Reply with quote

Hi,
I have to summarize data as below. Input file is sorted in ascending order on first 5 bytes (employee ID). Could you please let me know, how can we achieve the expected output thru JCL SORT ?

Input file:

Code:

12345,GEORGE,012118,      ,      ,
12345,GEORGE,      ,022818,      ,
12345,GEORGE,      ,      ,033118,
22222,ALAN  ,042018,      ,      ,
33333,ALEX  ,050118,      ,      ,
33333,ALEX  ,      ,      ,060118,
55555,JOHN  ,      ,      ,071818,


Expected Output
Code:

12345,GEORGE,012118,022818,033118,
22222,ALAN  ,042018,      ,      ,
33333,ALEX  ,050118,      ,060118,
55555,JOHN  ,      ,      ,071818,
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Sep 04, 2018 10:25 am
Reply with quote

Santhosh,

Can you have more than 3 duplicate records for an employee ID? If yes, can you show such an example.
Back to top
View user's profile Send private message
santhosh5983

New User


Joined: 11 Mar 2015
Posts: 10
Location: India

PostPosted: Tue Sep 04, 2018 11:36 am
Reply with quote

Arun,
We will have max of 3 duplicate records for 1 employee ID, i.e 1 row for each date if present (last 3 fields).

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

Senior Member


Joined: 29 Apr 2008
Posts: 2011
Location: USA

PostPosted: Tue Sep 04, 2018 8:17 pm
Reply with quote

Code:
 INREC  IFTHEN=(WHEN=(14,6,CH,EQ,C' '),OVERLAY=(14:C'000000'),   
                HIT=NEXT),                                       
        IFTHEN=(WHEN=(21,6,CH,EQ,C' '),OVERLAY=(21:C'000000'),   
                HIT=NEXT),                                       
        IFTHEN=(WHEN=(28,6,CH,EQ,C' '),OVERLAY=(28:C'000000'))   
 SORT FIELDS=(1,5,CH,A)                                           
 SUM  FIELDS=(14,6,ZD,                                           
              21,6,ZD,                                           
              28,6,ZD)                                           
 OUTREC IFTHEN=(WHEN=(14,6,ZD,EQ,+0),OVERLAY=(14:C'      '),     
                HIT=NEXT),                                       
        IFTHEN=(WHEN=(21,6,ZD,EQ,+0),OVERLAY=(21:C'      '),     
                HIT=NEXT),                                       
        IFTHEN=(WHEN=(28,6,ZD,EQ,+0),OVERLAY=(28:C'      '))     
 END                                                             


Code:
********************************* TOP OF DATA ******
12345,GEORGE,012118,022818,033118,                 
22222,ALAN  ,042018,      ,      ,                 
33333,ALEX  ,050118,      ,060118,                 
55555,JOHN  ,      ,      ,070118,                 
******************************** BOTTOM OF DATA ****
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2011
Location: USA

PostPosted: Tue Sep 04, 2018 9:20 pm
Reply with quote

Code:
 SORT FIELDS=COPY                                   
 OUTFIL REMOVECC,NODETAIL,                           
        SECTIONS=(1,5,                               
        TRAILER3=(1,13,                             
                 MAX=(14,6,ZD,EDIT=(TTTTTT)),C',',   
                 MAX=(21,6,ZD,EDIT=(TTTTTT)),C',',   
                 MAX=(28,6,ZD,EDIT=(TTTTTT)),C',')) 
 END                                                 


Code:
********************************* TOP OF DATA ****
12345,GEORGE,012118,022818,033118,               
22222,ALAN  ,042018,000000,000000,               
33333,ALEX  ,050118,000000,060118,               
55555,JOHN  ,000000,000000,070118,               
******************************** BOTTOM OF DATA **
Code:
 SORT FIELDS=COPY                                   
 OUTFIL REMOVECC,NODETAIL,                           
        SECTIONS=(1,5,                               
        TRAILER3=(1,13,                             
                 MAX=(14,6,ZD,EDIT=(IIIIII)),C',',   
                 MAX=(21,6,ZD,EDIT=(IIIIII)),C',',   
                 MAX=(28,6,ZD,EDIT=(IIIIII)),C',')) 
 END                                                 
Code:
********************************* TOP OF DATA *****
12345,GEORGE, 12118, 22818, 33118,                 
22222,ALAN  , 42018,      ,      ,                 
33333,ALEX  , 50118,      , 60118,                 
55555,JOHN  ,      ,      , 70118,                 
******************************** BOTTOM OF DATA ***
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Sep 04, 2018 11:14 pm
Reply with quote

Santhosh,

Assuming the input to be FB/LRECL=80, Here is a sort-copy version that would produce your expected output.
Code:
  OPTION COPY                                                     
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(81:2Z,14,6,2Z,21,6,2Z,28,6)) 
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                           
  SECTIONS=(1,5,TRAILER3=(1,13,MAX=(81,8,BI,TO=BI,LENGTH=6),',', 
                               MAX=(89,8,BI,TO=BI,LENGTH=6),',', 
                               MAX=(97,8,BI,TO=BI,LENGTH=6),','))
SORTOUT had
Code:
12345,GEORGE,012118,022818,033118,
22222,ALAN  ,042018,      ,      ,
33333,ALEX  ,050118,      ,060118,
55555,JOHN  ,      ,      ,071818,
Back to top
View user's profile Send private message
santhosh5983

New User


Joined: 11 Mar 2015
Posts: 10
Location: India

PostPosted: Thu Sep 06, 2018 3:15 pm
Reply with quote

sergeyken and Arun,
Thank you very much for the solution! Much appreciated.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Grouping by multiple headers DFSORT/ICETOOL 7
No new posts JCL EXEC PARM data in C Java & MQSeries 2
Search our Forums:

Back to Top