Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to summarize data by grouping on a key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: How to summarize data by grouping on a key
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: 2407
Location: @my desk

PostPosted: Tue Sep 04, 2018 10:25 am    Post subject: Reply to: How to summarize data by grouping on a key
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    Post subject: Reply to: How to summarize data by grouping on a key
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

Active User


Joined: 29 Apr 2008
Posts: 364
Location: Maryland

PostPosted: Tue Sep 04, 2018 8:17 pm    Post subject:
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

Active User


Joined: 29 Apr 2008
Posts: 364
Location: Maryland

PostPosted: Tue Sep 04, 2018 9:20 pm    Post subject:
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: 2407
Location: @my desk

PostPosted: Tue Sep 04, 2018 11:14 pm    Post subject:
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    Post subject: Reply to: How to summarize data by grouping on a key
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Extra data in overlay field speermb DFSORT/ICETOOL 2 Thu Sep 13, 2018 6:04 pm
No new posts CLOB Column data to External File usi... lalitmehta12 DB2 9 Tue Aug 21, 2018 7:22 pm
No new posts JCL to send email along with formated... wik123 JCL & VSAM 5 Thu Aug 09, 2018 9:48 am
No new posts Send data from a CICS TS program to M... spiThingy CICS 2 Wed Aug 08, 2018 8:58 am
This topic is locked: you cannot edit posts or make replies. Data Sets are files. Please stop str... jasorn JCL & VSAM 11 Wed Aug 08, 2018 1:55 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us