DATA Step, Macro, Functions and more

excel to sas date issue

Reply
Super Contributor
Posts: 673

excel to sas date issue

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

Re: excel to sas date issue

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
Super Contributor
Posts: 673

Re: excel to sas date issue

Posted in reply to CurtisMack
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;
Frequent Contributor
Posts: 102

Re: excel to sas date issue

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.
Super Contributor
Posts: 673

Re: excel to sas date issue

Posted in reply to CurtisMack
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
Valued Guide
Posts: 2,177

Re: excel to sas date issue

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
Super Contributor
Posts: 673

Re: excel to sas date issue

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.
Valued Guide
Posts: 2,177

Re: excel to sas date issue

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
Ask a Question
Discussion stats
  • 7 replies
  • 189 views
  • 0 likes
  • 3 in conversation