Date: Thu, 30 Jul 2009 17:44:34 -0400
Reply-To: Ya Huang <ya.huang@AMYLIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ya Huang <ya.huang@AMYLIN.COM>
Subject: Re: SAS DateTime Format that sorts correctly is IS8601dt.
Dan,
I totally understand the case you presented below.
I guess my question now is "IF we only have one var with
value of complete ISO datetime mixed with partial ISO datetime,
do we always get the order AS IF the partial is filled
with 0 or 0s?". For example, 2006 ordered first, because
it is treated as if 2006-01-01T00:00:00.
It seems to be the case for me, but I'm not sure. Maybe ISO
format is designed to be consistent with the ASCII code
rank order?
Ya
On Thu, 30 Jul 2009 14:24:37 -0700, Nordlund, Dan (DSHS/RDA)
<NordlDJ@DSHS.WA.GOV> wrote:
>> -----Original Message-----
>> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>> Huang, Ya
>> Sent: Thursday, July 30, 2009 1:24 PM
>> To: SAS-L@LISTSERV.UGA.EDU
>> Subject: Re: SAS DateTime Format that sorts correctly is IS8601dt.
>>
>> Thanks to all who responded.
>>
>> My original question was more of a philosophical question. Though it was
>>
>> triggered by a question of sorting an ISO 8601 format date. My
>> understanding
>> to ISO 8601 format is that a datetime can be presented as a partial
>> datetime:
>>
>> a complete date/time: 2006-11-04T10:29:23
>> missing second: 2006-11-04T10:29
>> missing all time: 2006-11-04
>> missing month: 2006
>>
>> If the date is a complete ISO 8601 date, then I'm quite sure
>> that sorting the string should get the same result as sorting the
>> underline numeric. But when the partial date is involved, I'm not
>> sure anymore. To make it even more complicated, imagine the string is
>> not
>> left aligned!
>>
>> data xx;
>> length dt $50;
>> dt='2006-11-04T10:29:00'; output;
>> dt='2006-11-04T10:29:23'; output;
>> dt='2006-11-04T10:29'; output;
>> dt='2006-11-04T10'; output;
>> dt='2006-11-04'; output;
>> dt='2006-11'; output;
>> dt='2006'; output;
>> dt=' 2006'; output;
>> dt=' 2006-11'; output;
>> dt=' 2006-11-04'; output;
>> dt=' 2006-11-04T10'; output;
>> run;
>>
>> proc sort;
>> by dt;
>> run;
>>
>> proc print;
>> run;
>>
>> Obs dt
>>
>> 1 2006-11
>> 2 2006-11-04
>> 3 2006
>> 4 2006-11-04T10
>> 5 2006
>> 6 2006-11
>> 7 2006-11-04
>> 8 2006-11-04T10
>> 9 2006-11-04T10:29
>> 10 2006-11-04T10:29:00
>> 11 2006-11-04T10:29:23
>>
>> It shows that if the string is left aligned, the sorting seems
>> to make sense. I'm 99.9% sure the sorting is 'right'. But I can't
>> be 100% sure it's always the case.
>>
>> Thanks
>>
>> Ya
>>
>>
><<<snip>>>
>
>If the string representation is only a partial representation of the
underlying datetime value, there is no way to guarantee that sorting by the
string representation will sort the same way as sorting on the full
datetime value. And in fact one could easily end up with the incorrect
sort order. Consider, the following two colums of data, a full datetime
value and a partial string datetime representation.
>
>2006-12-05T00:00:00 2006
>2006-11-04T10:29:23 2006-11-04
>2006-11-04T10:00:00 2006-11-04T10:00:00
>2006-10-01T00:00:00 2006-10-01T00:00:00
>2007-11-04T10:29:23 2007
>
>If you sort by the partial values, the order will be incorrect for the
full datetime values.
>
>Dan
>
>Daniel J. Nordlund
>Washington State Department of Social and Health Services
>Planning, Performance, and Accountability
>Research and Data Analysis Division
>Olympia, WA 98504-5204
| 
