Date:         Thu, 30 Jul 2009 14:24:37 -0700
Reply-To:     "Nordlund, Dan (DSHS/RDA)" <NordlDJ@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Nordlund, Dan (DSHS/RDA)" <NordlDJ@DSHS.WA.GOV>
Subject:      Re: SAS DateTime Format that sorts correctly is IS8601dt.
In-Reply-To:  <B84879D70E8C1C418A6C8DA90F73313403725DFD@A-EXCH-VS1.amylin.com>
Content-Type: text/plain; charset=windows-1252

> -----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