Date:         Mon, 9 Aug 2004 13:59:07 -0600
Reply-To:     Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject:      Re: using Excel time dates in SAS
Comments: To: pchoate@DDS.CA.GOV
Content-Type: text/plain; charset=us-ascii

I don't think that Excel owes its place at the top of the heap entirely
to Microsoft business practices.  It's a good product overall; it just
has some flaws.  The spreadsheet market was Lotus's to lose, and through
poor management they lost it.

SAS has a similar date problem, but it occurs outside the date range
that most users care about.




--
JackHamilton@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA

>>> "Choate, Paul@DDS" <pchoate@DDS.CA.GOV> 08/09/2004 11:52 AM >>>
Maybe that's because due to Microsoft business practices Excel is
standard
in business, education, and government worldwide.

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Jack
Hamilton
Sent: Monday, August 09, 2004 10:31 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: using Excel time dates in SAS

That's correct - Excel 2000, and probably other versions, treats 1900
as
a leap year.

The relevant law for the United Kingdom and the colonies (including
America) is 24 Geo. 2. c. 23 [amended by 25 Geo. 2. c. 30.]:

   Be it further enacted by the Authority aforesaid,

   That the several Years of our Lord, 1800, 1900, 2100, 2200,
   2300, or any other hundredth Years of our Lord, which
   shall happen in Time to come, except only every fourth
   hundredth Year of our Lord, whereof the Year of our
   Lord 2000 shall be the first, shall not be esteemed or
   taken to be Bissextile or Leap Years, but shall be
   taken to be common Years, consisting of 365 Days,
   and no more;

   and that the Years of our Lord 2000, 2400, 2800, and
   every other fourth hundred Year of our Lord, from the
   said Year of our Lord 2000 inclusive, and also all other
   Years of our Lord, which by the present Supputation
   are esteemed to be Bissextile or Leap Years, shall
   for the future, and in all Times to come, be esteemed
   and taken to be Bissextile or Leap Years, consisting
   of 366 Days, in the same Sort and Manner as is now
   used with respect to every fourth Year of our Lord.

("supputation" - now there's a word you don't see every day - it's not
even in my unabridged dictionary)

I wonder why anyone uses Excel to do advanced calculations, given that
it gets something as elementary and important as leap years wrong.




--
JackHamilton@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA

>>> "F. J. Kelley" <jkelley@UGA.EDU> 08/09/2004 8:33 AM >>>
I believe Excel also treats 1900 as a leap year, so you may
have to check that.  I believe SAS will import excel dates
correctly however, as long as the whole column is formatted
as a date.  The only time I had to perform the conversion
myself was when I had a spreadsheet with select cells
formatted as dates.

---- Original message ----
>Date: Mon, 9 Aug 2004 11:08:07 -0400
>From: John Fleming <johntwrl@HOTMAIL.COM>
>Subject: Re: using Excel time dates in SAS
>To: SAS-L@LISTSERV.UGA.EDU
>
>On Sun, 8 Aug 2004 16:07:47 -0700, natesa
<natesaprasad@GMAIL.COM> wrote:
>
>>I use enterprise guide and tried linking data in excel to
SAS data
>>sets with Date as the link.
>>
>>The problem is SAs uses 1960 as cut off while Excel uses
1900.
>>Date cells dont link as Excel/ Sas or after Excelfiles are
imported as
>>new SAs data sets.
>>
>>Please help
>>
>>Natesa
>
>Natesa, the solution is really quite simple.
>
>As you have observed, SAS counts the number of days that
have elapsed since
>January 1, 1960.  SAS stores this as a numeric variable.
>
>Excel, operating on a Windows platform, counts the number of
days that have
>elapsed since January 1, 1900--usually.  Excel can also be
set to count the
>number of days since January 1, 1904, which I believe is the
default for
>Excel running on a Macintosh.  Whatever setting, Excel
stores this as a
>numberic variable.
>
>There is a fixed number of days between January 1, 1900 and
January 1, 1960.
>
>Hence, when you import data from Excel, to get the SAS date
correct, you
>need to adjust the value by the number of days between
January 1, 1900, and
>January 1, 1960 to get the date correct in SAS.
>
>For example, say your Excel date value is stored in the
variable XDATE.
>
>You might use a bit of code that looks like this.
>
>XDATE = XDATE - ('01JAN1960'd - '01JAN1900'd);
>
>--
>John Fleming
>Alberta Solicitor General
>Edmonton, Canada