Posted: Tue Sep 14, 2004 10:00 am Post subject: DFSORT: Odd date comparison problem
I have an 80-byte RECFM=FB file that contains, among other values, 3 dates
In columns 26-29, there is a packed date in the form:
0MYD
MYDC
for example,
0842
000C is 080402 or August 2, 2004.
I'd prefer to receive this date in a more standard format, but unfortunately I don't have that option.
The two other dates in the file are:
In columns 30-39, a date in the form YYYY-MM-DD, eg. 2004-08-01
In columns 40-49, a date in the form YYYY-MM-DD, e.g. 2004-08-31
What I'm trying to accomplish is to include only records from this file where the packed date in 26-29 is greater than or equal to the date in 30-39 and less than or equal to the date in 40-49
Is this something that can be done using DFSort/Icetool? I have been trying to figure this out using the techniques in the paper: DFSORT's Year 2000 Features (The New Generation), but frankly don't understand the paper too well, and there is the added complication of the packed date being in a format for which there is no full date format defined, as far as I can tell.
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Tue Sep 14, 2004 10:54 am Post subject:
Here's a DFSORT job that will do what you asked for. I'm assuming that all of your dates are 20yy dates. If you have 19yy and 20yy dates, then we could change the job accordingly to handle that.
Code:
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file
//SORTOUT DD DSN=... output file
//SYSIN DD *
OPTION COPY
* Convert P'mmyydd' date to C'MMYYDD' date in 81-86
INREC FIELDS=(1,80,81:26,4,PD,TO=ZD,LENGTH=6)
* Convert C'MMYYDD' date to C'20YY-MM-DD' date in 81-90
OUTREC FIELDS=(1,80,81:C'20',83,2,C'-',81,2,C'-',85,2)
* Comapare YYYY-MM-DD dates
OUTFIL INCLUDE=(81,10,CH,GE,30,10,CH,AND,
81,10,CH,LE,40,10,CH),
* Remove added date
OUTREC=(1,80)
/*
_________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Tue Sep 14, 2004 10:55 am Post subject:
sterling price,
The following DFSORT/ICETOOL Jcl will give you the desired results. Since your input date is not the standard format date, we need to reformat it and make it 4 digit year so that we can use to compare it against the 10 character date field.
A brief explanation of the job. The first copy operator takes in your input file and expands the Packed decimal date field to numeric format using INREC FIELDS.
Using OUTREC FIELDS we re-arrange that converted date field, so that we can expand the 2 digit year to 4 digit year. The rearrangement is as (YYMMDD). Now using OUTREC on OUTFIL we convert the 2 digit year to 4 digit year using the Y2T(-) Format.
The next copy step then compares the expanded date with dates at 30 and 40th position and writes the records stripping the expanded date at the end.
Code:
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=YOUR INPUT FILE,
// DISP=SHR
//T1 DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(C,Y),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN) USING(CTL1)
COPY FROM(T2) USING(CTL2)
//CTL1CNTL DD *
CENTWIN=1950
INREC FIELDS=(1,80,26,4,PD,EDIT=(TTTTTT))
OUTREC FIELDS=(1,80, $ FIRST 80 BYTES AS IS
83,2, $ YEAR
81,2, $ MONTH
85,2) $ DAY
OUTFIL FNAMES=T1,
OUTREC=(1,80, $ FIRST 80 BYTES AS IS
81,6,Y2T(-)) $ CONVERT THE DATE TO YYYY-MM-DD
//CTL2CNTL DD *
INCLUDE COND=(81,10,CH,GE,30,10,CH,AND,
81,10,CH,LE,40,10,CH)
OUTFIL FNAMES=OUT,OUTREC=(1,80)
/*
Thank you both very much. I am reviewing your code to make sure I understand before I integrate it into my job. As a side note, these dates are all for the month previous to the current month, so they will all contain be only for century 20.
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Tue Sep 14, 2004 11:46 am Post subject:
Kolusu said
Quote:
Are you assuming that all records have the year greater than or equal to zero? I guess sterling price might have records for the years less than 2000
Kolusu,
I said in my post
Quote:
I'm assuming that all of your dates are 20yy dates. If you have 19yy and 20yy dates, then we could change the job accordingly to handle that.
Guess you missed that. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Tue Sep 14, 2004 3:01 pm Post subject:
Quote:
Can we avoid the extra pass if the data consists of 19yy and 20yy dates ?
Sure. It just takes a couple of tweaks of the DFSORT job I showed previously as follows (CENTWIN=1950 and Y2C added):
Code:
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file
//SORTOUT DD DSN=... output file
//SYSIN DD *
OPTION COPY,CENTWIN=1950
* Convert P'mmyydd' date to C'MMYYDD' date in 81-86
INREC FIELDS=(1,80,81:26,4,PD,TO=ZD,LENGTH=6)
* Convert C'MMYYDD' date to C'YYYY-MM-DD' date in 81-90
OUTREC FIELDS=(1,80,81:83,2,Y2C,C'-',81,2,C'-',85,2)
* Compare YYYY-MM-DD dates
OUTFIL INCLUDE=(81,10,CH,GE,30,10,CH,AND,
81,10,CH,LE,40,10,CH),
* Remove added date
OUTREC=(1,80)
/*
_________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
sorry. I have another quick question here. Can we avoid the extra pass if the data consists of 19yy and 20yy dates ?