Date:         Thu, 30 Jul 2009 13:23:32 -0700
Reply-To:     "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Subject:      Re: SAS DateTime Format that sorts correctly is IS8601dt.
Comments: To: "Hixon, John" <jhixon@amgen.com>
In-Reply-To:  <6DBE5CA7599DEB4E8CDB656893E510D52D4C2BCF20@usri-pmsg-mbs02.am.corp.amgen.com>
Content-Type: text/plain; charset="us-ascii"

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



-----Original Message-----
From: Hixon, John [mailto:jhixon@amgen.com]
Sent: Thursday, July 30, 2009 12:56 PM
To: Huang, Ya; SAS-L@LISTSERV.UGA.EDU
Subject: SAS DateTime Format that sorts correctly is IS8601dt.


/*

Ya wrote:

----- Original Message -----
From: "Ya Huang" <ya.huang@AMYLIN.COM>
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Thursday, July 30, 2009 11:33
Subject: consistency of sorting a date or time var in num and char


> Hi there,
>
> Obviously sorting a date var as numeric is different from a date9.
> formatted character, since '03JAN2009' is ordered after '03APR2009'.
> But '03JAN2009'DT should have the same order as '01032009'. For time
> var, a time5. formatted string will not have the same order as the
> underline numeric var, since time5. is not zero padded.
>
> I wonder what kind of formatted date, time or datetime var have the
> same order as the underline numeric var.
>
> Thanks
>
> Ya
>

Hi Ya,

The formats you aree looking for are the IS8601 formats.

For DateTime the format is is8601dt.
For date:       is8601da.
For Time:       is8601tm.;

See example below for DateTime.

HTH?

Cheers,

John Hixon
jhixon@amgen.com

*/
data junk;
        do year=1950 to 2025 by 25;
        do month=1 to 12 by 4;
        do day=5 to 30 by 5;
        do hour=1 to 23 by 11;
        do minute=1 to 59 by 29;
        do second=1 to 59 by 29;

DateTimeC=put(year,z4.)||"-"||put(month,z2.)||"-"||put(day,z2.)||"T"||pu
t(hour,z2.)||":"||put(minute,z2.)||":"||put(second,z2.);
                DateTimeN=input(DateTimeC,is8601dt.);
                DateTimeN_withFormat=DateTimeN;
                output;
        end; end; end; end; end; end;
 run;
proc print data=junk;
format dateTimeN_withFormat is8601dt.;
run;