MVSFORUMS.com :: View topic - Sort comma dlmtd fl & only last record per key in O/P

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

Sort comma dlmtd fl & only last record per key in O/P

MVSFORUMS.com Forum Index -> Utilities

View previous topic :: View next topic

Author

Message

umj
Beginner

Joined: 25 Nov 2008
Posts: 8
Topics: 4

Posted: Mon Oct 05, 2009 10:18 am Post subject: Sort comma dlmtd fl & only last record per key in O/P

Hi, I have a comma delimited file. The layout of the file is:
(The key columns have an asterisk)

Code:

*TACTIC EVNT ID CHAR 20 X(20)
*TACTIC EVNT ID TYP CD Numeric 5
*TACTIC EVNT SRVC ID Numeric 5
*TACTIC ID CHAR 50 X(50)
*STRTGY SRC CD CHAR 2 X(2)
*TRGT TYP CD CHAR 3 X(3)
SUPRS_CD_1 Numeric 3 9(3)
SUPRS_DT_1 Date 8 YYYYMMDD
SUPRS_CD_2 Numeric 3 9(3)
SUPRS_DT_2 Date 8 YYYYMMDD
SUPRS_CD_3 Numeric 3 9(3)
SUPRS_DT_3 Date 8 YYYYMMDD
SUPRS_CD_4 Numeric 3 9(3)
SUPRS_DT_4 Date 8 YYYYMMDD
SUPRS_CD_5 Numeric 3 9(3)
SUPRS_DT_5 Date 8 YYYYMMDD
SUPRS_CD_6 Numeric 3 9(3)
SUPRS_DT_6 Date 8 YYYYMMDD
Timestamp Timestamp 19 YYYY-MM-DD HH:MM:SS

I need to create an output file such that it has only the latest record for each key value.

Thus I need to sort the file by the key fields and the timestamp field. In order to be able to SORT I have to fiest convert the file to a positional one. Then I want to use only the LAST record for each key in order to get the latest record.

Thus I tried using SORT:

Code:

SORT FIELDS=(1,20,CH,A,21,5,UFF,A,26,5,UFF,A,31,50,CH,A,
81,2,CH,A,83,3,CH,A,152,19,CH,A)
INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=20),
%02=(ENDBEFR=C',',FIXLEN=5),
%03=(ENDBEFR=C',',FIXLEN=5),
%04=(ENDBEFR=C',',FIXLEN=50),
%05=(ENDBEFR=C',',FIXLEN=2),
%06=(ENDBEFR=C',',FIXLEN=3),
%07=(ENDBEFR=C',',FIXLEN=3),
%08=(ENDBEFR=C',',FIXLEN=8),
%09=(ENDBEFR=C',',FIXLEN=3),
%10=(ENDBEFR=C',',FIXLEN=8),
%11=(ENDBEFR=C',',FIXLEN=3),
%12=(ENDBEFR=C',',FIXLEN=8),
%13=(ENDBEFR=C',',FIXLEN=3),
%14=(ENDBEFR=C',',FIXLEN=8),
%15=(ENDBEFR=C',',FIXLEN=3),
%16=(ENDBEFR=C',',FIXLEN=8),
%17=(ENDBEFR=C',',FIXLEN=3),
%18=(ENDBEFR=C',',FIXLEN=8),
%19=(ENDBEFR=C',',FIXLEN=19)),
BUILD=(%01,%02,%03,%04,%05,%06,%07,%08,%09,%10,
%11,%12,%13,%14,%15,%16,%17,%18,%19)

With the above statements I am able to convert the file to a positional oen and then SORT it. But is there a way by which I can have only the LAST record for wach key in the output file. Or do I need to create another step to do it.

Thanks

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

Posted: Mon Oct 05, 2009 10:43 am Post subject:

umj,

Use the following DFSORT/ICETOOL JCL to get the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=your input file,DISP=SHR
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) LAST ON(1,20,CH) ON(21,5,UFF) -
ON(26,5,UFF) ON(31,50,CH) ON(81,5,CH) USING(CTL1)
//CTL1CNTL DD *
SORT FIELDS=(1,20,CH,A,21,5,UFF,A,26,5,UFF,A,31,50,CH,A,
81,2,CH,A,83,3,CH,A,152,19,CH,A)
INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=20),
%02=(ENDBEFR=C',',FIXLEN=5),
%03=(ENDBEFR=C',',FIXLEN=5),
%04=(ENDBEFR=C',',FIXLEN=50),
%05=(ENDBEFR=C',',FIXLEN=2),
%06=(ENDBEFR=C',',FIXLEN=3),
%07=(ENDBEFR=C',',FIXLEN=3),
%08=(ENDBEFR=C',',FIXLEN=8),
%09=(ENDBEFR=C',',FIXLEN=3),
%10=(ENDBEFR=C',',FIXLEN=8),
%11=(ENDBEFR=C',',FIXLEN=3),
%12=(ENDBEFR=C',',FIXLEN=8),
%13=(ENDBEFR=C',',FIXLEN=3),
%14=(ENDBEFR=C',',FIXLEN=8),
%15=(ENDBEFR=C',',FIXLEN=3),
%16=(ENDBEFR=C',',FIXLEN=8),
%17=(ENDBEFR=C',',FIXLEN=3),
%18=(ENDBEFR=C',',FIXLEN=8),
%19=(ENDBEFR=C',',FIXLEN=19)),
BUILD=(%01,%02,%03,%04,%05,%06,%07,%08,%09,%10,
%11,%12,%13,%14,%15,%16,%17,%18,%19)
//*

_________________
Kolusu
www.linkedin.com/in/kolusu

umj
Beginner

Joined: 25 Nov 2008
Posts: 8
Topics: 4

Posted: Mon Oct 05, 2009 11:30 am Post subject:

Thanks a lot Kolusu. It worked !

Display posts from previous:

	MVSFORUMS.com Forum Index -> Utilities	All times are GMT - 5 Hours
Page 1 of 1

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