topic Re: excel to sas date issue in SAS Programming

excel to sas date issue

SASPhile — Tue, 13 Oct 2009 14:48:56 GMT

the date in excel is 08/05/2009 when and it gets converted as 40030.724294 in SAS.This is strange!

Re: excel to sas date issue

CurtisMack — Tue, 13 Oct 2009 15:05:01 GMT

I agree, very odd. 40030 is actually that SAS date for 08/06/2069 which ia a little too similar to 08/05/2009 for me to think it is just coincidence. Is there anything else you can offer to help explain this?
--------------------------------------------------
data _null_;
a = 40030;
format a YYMMDD10.;
put a;
run;
-------------------------------
2069-08-06

Re: excel to sas date issue

SASPhile — Tue, 13 Oct 2009 15:11:24 GMT

well..I'm just using the folliwng code:and it results in the date conversion.
I even tried the proc import and it resulted the same.
%macro test;
proc sql;
connect to excel (path="&inpt.\Walgreens 2009 08.xls"
header=no mixed=yes );

create table walgreens as
select * from connection to excel
(select * from [%sysfunc(dequote(%superQ(wksht)))]);
disconnect from excel;
quit;
%mend test;
%test;

Re: excel to sas date issue

CurtisMack — Tue, 13 Oct 2009 15:28:54 GMT

I would check the Excel table to make sure that all of the dates in that column are entered in the same manner. If they have different formats, that might explain this. If not, I think I would give this one to tech support.

Re: excel to sas date issue

SASPhile — Tue, 13 Oct 2009 16:51:28 GMT

8/5/2009 5:22:59 PM ..this is how the data is entered in excel, but we see it as 08/05/2009.in sas it gets converted as 40030.724294

Re: excel to sas date issue

Peter_C — Wed, 14 Oct 2009 06:47:51 GMT

sounds like SAS is reading the internal exccel value, without recognising the datatype and .applying the standard adjustment (1/1/1960-1/1/1900 with the fraction representing the fraction of the day at the time point.
Although we can fix it, I hope SAS Customer Support can provide the option to enable appropriate conversion to be automatic

Re: excel to sas date issue

SASPhile — Wed, 14 Oct 2009 14:06:00 GMT

if index(name,'Novo Distribution')

Can we make the search not case sensitive?
Like if the argument-2 here 'Novo Distribution' can be in any case.

Re: excel to sas date issue

Peter_C — Wed, 14 Oct 2009 14:55:42 GMT

you can apply the upcase() or lowcase() functions to the NAME variable and test against the relevant constant, but you can adapt the FIND() function:
for case insensitive FIND see it's on-line doc (even available for SAS913 at http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a002267763.htm#a002504497 )

PeterC Message was edited by: Peter.C