Date:         Thu, 8 Sep 2005 09:04:21 -0700
Reply-To:     "Nordlund, Dan" <NordlDJ@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Nordlund, Dan" <NordlDJ@DSHS.WA.GOV>
Subject:      Re: Cant read a Datetime using Datetime Format
Comments: To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Content-Type: text/plain

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Howard
Schreier <hs AT dc-sug DOT org>
Sent: Thursday, September 08, 2005 5:33 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Cant read a Datetime using Datetime Format

Peter has reminded me that the DATESTYLE system option is useful here. If I
code

   options datestyle=dmy;

my results are

   dt=30MAR1998:00:00:00 _ERROR_=0 _INFILE_=30/3/1998 _N_=1
   dt=31MAR1998:00:00:00 _ERROR_=0 _INFILE_=31/3/1998 _N_=2
   dt=01APR1998:00:00:00 _ERROR_=0 _INFILE_=1/4/1998 _N_=3

as intended.

In the absence of any such guidance, a human being would try to resolve the
ambiguity by looking at other records, where one segment or the other is
greater than 12. I was not suggesting that the ANYDTDTM informat should do
so; that would be a big change in the nature and role of an informat. I was
just pointing out that ANYDTDTM is not a panacea.

In fact, ANYDTDTM remains a SAS Trap. Before it came along, one would
typically eyeball a few records, discern a pattern, and code either MMDDYY
or DDMMYY according to that pattern; any exception would call attention to
itself through "invalid data" notes. Now one can code ANYDTDTM, but it's
the responsibility of the coder to have the appropriate state for the
DATESTYLE option; there will be no notes in the log to identify problems.

It's actually a bit worse than I thought. It seems that ANYDTDTM does not
do any validity checking. Here's an example where the value conforms to
neither MDY nor DMY:

   data _null_;
   input dt : anydtdtm.;
   put _all_;
   cards;
   20/20/2020
   ;

The result is

   dt=. _ERROR_=0 _N_=1

and there is no note about invalid data. So it's up to the user to check
for failed conversions (unless I've missed another option -:) ).

----------Reply--------------

Howard et al.,

The datestyle option appears to only affect the those dates which are
ambiguous.  If the date is unambiguous, the anydtdtm format will convert the
date regardless of whether the date fits the style or not.  Let's add the
date, 1/31/1998 to the example that Howard gave.

Options datestyle=dmy;

data _null_;
  input dt : anydtdtm.;
  format dt datetime20.;
  put _infile_ @1 _all_;
cards;
30/3/1998
31/3/1998
1/4/1998
1/31/1998
;
run;

Here are my results:

dt=30MAR1998:00:00:00 _ERROR_=0 _INFILE_=30/3/1998 _N_=1
dt=31MAR1998:00:00:00 _ERROR_=0 _INFILE_=31/3/1998 _N_=2
dt=01APR1998:00:00:00 _ERROR_=0 _INFILE_=1/4/1998 _N_=3
dt=31JAN1998:00:00:00 _ERROR_=0 _INFILE_=1/31/1998 _N_=4

So the date 1/31/1998 is converted to a valid SAS date, even though it is
likely an error, since it was indicated that dates were supposed to be dmy
style.  I would rather get an error, or at least a warning that this has
happened.

Dan

Daniel J. Nordlund
Research and Data Analysis
Washington State Department of Social and Health Services
Olympia, WA  98504-5204