Date:         Thu, 2 Sep 2004 09:15:31 -0700
Reply-To:     "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject:      SAS date and time trivia  - was RE: deleting records from MS Acce
              ss table
Comments: To: "Miller, Jeremy T." <zyp9@CDC.GOV>

Hi Jeremy -

RE "SAS even puts it at 1960" - this is what SAS will do to a sasdate that
is formatted as a SAS time.  You need to convert your dates to datetime or
visa versa:

Datetime= dhms(date,0,0,0);
Or
Date=datepart(datetime);

Cosider this:

43   data _null_;
44        date1=date();
45        date2=date();
46        date3=date();
47        datetime1=datetime();
48        datetime2=datetime();
49        datetime3=datetime();
50
51        format date2 datetime2 date.;
52        format date3 datetime3 datetime.;
53
54        put (_all_) (=/);
55
56    run;


date1=16316
date2=02SEP04
date3=01JAN60:04:31:56   <<<"SAS even puts it at 1960"<<<

datetime1=1409732503.6
datetime2=*******
datetime3=02SEP04:08:21:44

A date is the number of days since 1960, so today is 16,316.  A datetime is
the number of seconds from 1960, so the second I ran this was 1,409,732,503.
The datetime number was too large for SAS to understand as a date so it
gives "*******".

Dates in SAS fail on the 6589336th day after 1/1/60, which is 1/1/20001:

226  data _null_;
227  format date date.;
228  do i = 6589331 to 6589341 by 1;
229      date=i;
230      year=year(date);
231       put _all_;
232  end;
233  run;

date=27DEC00 i=6589331 year=20000 _ERROR_=0 _N_=1
date=28DEC00 i=6589332 year=20000 _ERROR_=0 _N_=1
date=29DEC00 i=6589333 year=20000 _ERROR_=0 _N_=1
date=30DEC00 i=6589334 year=20000 _ERROR_=0 _N_=1
date=31DEC00 i=6589335 year=20000 _ERROR_=0 _N_=1
NOTE: Invalid argument to function YEAR at line 230 column 10.
date=******* i=6589336 year=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function YEAR at line 230 column 10.
date=******* i=6589337 year=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function YEAR at line 230 column 10.
date=******* i=6589338 year=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function YEAR at line 230 column 10.
date=******* i=6589339 year=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function YEAR at line 230 column 10.
date=******* i=6589340 year=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function YEAR at line 230 column 10.
date=******* i=6589341 year=. _ERROR_=1 _N_=1
date=******* i=6589342 year=. _ERROR_=1 _N_=1

Certain date formats fail on 1/1/10000.

SAS times fail at the same point, 569,318,630,400 seconds past 1/1/1960,
which is 1/1/20001.

466  data _null_;
467  format datetime datetime.;
468  do i = 569318630398 to 569318630402 by 1;
469      datetime=i;
470      year=year(datepart(datetime));
471       put _all_;
472  end;
473  run;

datetime=31DEC00:23:59:58 i=569318630398 year=20000 _ERROR_=0 _N_=1
datetime=31DEC00:23:59:59 i=569318630399 year=20000 _ERROR_=0 _N_=1
NOTE: Invalid argument to function DATEPART at line 470 column 15.
datetime=**************** i=569318630400 year=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function DATEPART at line 470 column 15.
datetime=**************** i=569318630401 year=. _ERROR_=1 _N_=1
NOTE: Invalid argument to function DATEPART at line 470 column 15.
datetime=**************** i=569318630402 year=. _ERROR_=1 _N_=1
datetime=**************** i=569318630403 year=. _ERROR_=1 _N_=1

I don't know why this is so, but there may be a good reason.  Maybe one of
the many SAS-L illuminati can say.  Any takers?

Regards -

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Miller,
Jeremy T.
Sent: Wednesday, September 01, 2004 8:07 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: deleting records from MS Access table

....
What I was going to do, was output a few temp files and use DBMS Copy to
replace the files, but I'm getting a huge problem with the dates.  The
dates arrive as MMDDYYYY, which SAS has no problems reading.  But, the
transfer to Access changes them to DATETIME19.--and that makes all the
dates 1960--I guess ATLAS shrugged.  Even if I format the date in the
temp SAS file to DATETIME19., SAS even puts it at 1960.  I figured that
SAS would have the date correctly and just leave the time at 12 am.
I've tried a lot of different things here, and nothing seems to work.
Funny though, the table in ACCESS I want to append to was originally
created with SAS using EXPORT.  I think ACCESS must store all of its
dates as a datetime.
...