excel to sas date issue

[SASPhile]

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

[CurtisMack]

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

[SASPhile]

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;

[CurtisMack]

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.

[SASPhile]

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

[Peter_C]

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

[SASPhile]

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.

[Peter_C]

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