Date: Thu, 30 Mar 2006 14:49:35 -0800
Reply-To: "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject: Converting dates or times between MSAccess/Excel and SAS
Content-Type: text/plain; charset="US-ASCII"
Hi,
re: was (Re: importing dates from MSAccess)
If the automated USEDATE does not do what you
want, or you have an older version of SAS that
may not support this option, then if we cover
how to convert between MS dates and SAS dates
then you can always handle the worst case of
doing it manually by hand. (or keep this on
hand for the day you do need to)
SAS and Microsoft date variables are both numeric.
They both can store DATE, and/or TIME, and/or
DATETIME in one numeric value. However, SAS and MS
store them differently. The good news is that these
formats are easy enough to understand that you can
convert them manually if you need to.
Rules:
- SAS date is stored as number of DAYS since 1/1/1960.
- SAS time is stored as number of SECONDS since midnight.
- SAS datetime is stored as number of SECONDS since midnight 1/1/1960.
- Microsoft Excel and Access store the number of days since 12/30/1899
to the left of the decimal point (the integer) and stores the time
to the right of the decimal point (the mantissa) representing the
decimal proportion of one day.
With these constants in minds you can:
To display a SAS date as a number in SAS:
Change the format to a number format
such as 6. or 30.20, etc. or just assign
into a new numeric variable that has
not had a format assigned yet.
To display a MSAccess/MSExcel date as number in MS:
Change the MS formatting to ##### or ######.##########
Convert SAS DATE to MS DATE:
SAS_date = 16890 (3/30/2006)
MS_date = SAS_date + 21916
which is 16890+21916=38806 and if
placed into Excel or Access as 38806
and then formatted in Excel or Access
as a date it would display as 3/30/2006
(conversion of number value can be done
prior or after exporting from SAS to MS)
Convert MS DATE to SAS DATE:
MS_date = 38806 (3/30/2006)
SAS_date = MS_date - 21916
which is 38806-21916=16890 and if
placed into SAS as 16890
and then formatted in SAS
as a date it would display as 3/30/2006
(conversion of number value can be done
prior or after exporting from MS to SAS)
Convert SAS TIME to MS TIME:
SAS_time = 47820 (1:17PM)
MS_time = 86400 / SAS_date
which is 86400/47820=.5534722222 and if
placed into Excel or Access as .5534722222
and then formatted in Excel or Access
as a time it would display as 1:17PM
(conversion of number value can be done
prior or after exporting from SAS to MS)
Convert MS TIME to SAS TIME:
MS_time = .5534722222 (1:17pm)
SAS_time = 86400 * MS_date
which is 86400*.5534722222=47820 and if
placed into Excel or Access as 47820
and then formatted in Excel or Access
as a time it would display as 1:17PM
(conversion of number value can be done
prior or after exporting from MS to SAS)
Convert SAS DATETIME to MS DATETIME:
SAS_datetime= 1459343820 (30MAR2006 13:17:00)
MS_datetime=(SAS_datetime-(int(SAS_datetime/86400)*86400))/86400
which is (1459343820-(int(1459343820/86400)*86400))/86400
=38806.5534722222 and if
placed into Excel or Access as 38806.5534722222
and then formatted in Excel or Access
as a time it would display as 3/30/2006 1:17:00 PM
(conversion of number value can be done
prior or after exporting from SAS to MS)
Convert MS TIME to SAS TIME:
MS_datetime = 38806.5534722222 (38806.5534722222)
SAS_time = (MS_datetime-21916)*86400
which is (38806.5534722222-21916)*86400=1459343820
and if placed into Excel or Access as 1459343820
and then formatted in Excel or Access
as a time it would display as 30MAR2006 13:17:00
(conversion of number value can be done
prior or after exporting from MS to SAS)
and once you get the idea which pieces are date and time
on both sides you can manually convert between datatypes
DATE,TIME,DATETIME as well as across platforms. i.e. the
above formulas are conversions across platform but staying
DATE-to-DATE, TIME-to-TIME, DATETIME-to-DATETIME. You can
certainly adjust the arithmetic to switch types as well.
Reminder: the unformatting dates to just a number, and
making the cross-platform adjustments is the conversion
portion, as noted above this can be done on either platform
before or after the plain number value has been exported or
imported to either platform. If the plain number value gets
to the other platform sucessfully just remember that the
cosmetics(formatting) needs to be applied so that it *looks*
like the date/time for that platform. (the unformatting step
above is a figure of speech you can do for visual sake, since
the date or time value on either platform is merely formatted
for cosmetic display, the number underneath is already just a
number and is ready for use as a number or conversion. You
just have to swap the contents between the two human-applied
information-schemes [as listed in Rules section above])
Hope this is helpful.
Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group
Russell
Global Leaders in Multi-Manager Investing
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
tanwan
Sent: Sunday, March 26, 2006 8:17 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: importing dates from MSAccess
Has anyone suggested, the USEDATE option?
Access stores date in a datetime format, from SAS's perspective. If
USEDATE=YES, then DATE. format is used for date/time columns in the data
source table while importing data. If USEDATE=NO, then DATETIME. format
is used for date/time.
PROC IMPORT OUT=tracy.wendy;
DATATABLE="tina"
DBMS=ACCESS2000 REPLACE;
DATABASE="c:\data\pqrstuv7" ;
USEDATE=YES;
RUN;
T
