|
View previous topic :: View next topic
|
| Author |
Message |
santhosh5983
New User
Joined: 11 Mar 2015
Posts: 10
Location: India
|
|
|
|
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 |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
Santhosh,
Can you have more than 3 duplicate records for an employee ID? If yes, can you show such an example. |
|
| Back to top |
|
|
santhosh5983
New User
Joined: 11 Mar 2015
Posts: 10
Location: India
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
| 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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
| 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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
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 |
|
|
santhosh5983
New User
Joined: 11 Mar 2015
Posts: 10
Location: India
|
|
|
|
sergeyken and Arun,
Thank you very much for the solution! Much appreciated. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
