Page 1 of 1
SORT records on date.
Posted: Wed Sep 09, 2015 9:40 pm
by SarvanaL
Hi,
I need to sort the records in a dataset based on date in ascending order. If there are three dates namely 31.1.1998, 2.12.1997,3.6.2014 then output should have 2.12.1997,31.1.1998,3.6.2014
I have used the sort card:
But the records are not getting sorted properly. Can you guys please suggest how to solve it.
Re: SORT records on date.
Posted: Wed Sep 09, 2015 10:49 pm
by Robert Sample
What format are the dates in? You cannot put together a SORT statement without knowing that. Whatever the format, you'll want to sort by year, month, day to get the data in ascending date sequence.
Re: SORT records on date.
Posted: Thu Sep 10, 2015 12:10 am
by William Collins
You'll need to PARSE the fields in INREC, then make a temporary extension to the record. Heed Robert's point about sorting in a logical order (year, month, day).
In OUTREC, use BUILD to return the record to its original size.
Re: SORT records on date.
Posted: Thu Sep 10, 2015 11:47 am
by SarvanaL
Robert Sample wrote:What format are the dates in? You cannot put together a SORT statement without knowing that. Whatever the format, you'll want to sort by year, month, day to get the data in ascending date sequence.
Dates are in DD.MM.YYYY format.
Re: SORT records on date.
Posted: Thu Sep 10, 2015 11:48 am
by SarvanaL
William Collins wrote:You'll need to PARSE the fields in INREC, then make a temporary extension to the record. Heed Robert's point about sorting in a logical order (year, month, day).
In OUTREC, use BUILD to return the record to its original size.
Thanks but what are you saying when you say "a temporary extension to the record"?
Re: SORT records on date.
Posted: Thu Sep 10, 2015 3:40 pm
by nicc
Internally to sort records can be made longer so if you have an 80 byte record and you want to add your reformatted date (CCYYMMDD) you would ask sort to put it in columns 81-88 (for VB records you extend by inserting the extension after the RDW and before the first byte of 'real' data. You the sort on that added data and remove it when writing the output.
Re: SORT records on date.
Posted: Thu Sep 10, 2015 3:57 pm
by enrico-sorichetti
if as reported by the TS
Dates are in DD.MM.YYYY format.
in a fixed position
why complicate things ?
Code: Select all
SORT FIELDS=(year_start_column,4,CH,A,month_start_column,2,CH,A,day_start_column,2,CH,A)
should be more than enough
Re: SORT records on date.
Posted: Thu Sep 10, 2015 4:07 pm
by William Collins
The sample data shown is 31.1.1998, 2.12.1997, 3.6.2014. Hence the need to PARSE (assuming the data shown is correct and the description of the data is not, strictly).