Inputing dates from excel.

[deleted_user]

Hello. Prompt, please. How to input date from excel if it is displayed there in an excel format?

[Peter_C]

should be automatic
by what method is your SAS reading excel?

[deleted_user]

Here an example:

options noxwait noxsync;
x 'c:\data.xls';
filename data dde 'excel|Sheet1!r1c1:r5c3';

data set1;
infile data;
input x y date;
run;

If in excel dates are in sas format, that is corresponding numbers it is wonderful.
But what to do if they are in excel format?

[Peter_C]

if your dde connection has to "input" the values, try informat anydtdte.
If it reads just a number, then the excel date basis is number oif days since the beginning of 1900, so the conversion in a SAS data step is
sas_date = (excel date as number) - '1Jan1900'd ;
(perhaps a day or two out as Microsoft treats 29-Feb-1900 as valid)

[deleted_user]

don't work with informat anydtdte

[SASKiwi]

Curious to know why you are using DDE as compared to say PROC IMPORT where dates are handled automatically.

[Cynthia_sas]

I agree. I'm not sure why you would use DDE for such a simple table instead of PROC IMPORT or the SAS Excel LIBNAME engine.

The worst case scenario if you import a date (using import methods) that is not defined as an Excel date field is that you would have to adjust the value, as explained in this note:
http://support.sas.com/kb/41/000.html

Usually, you can tell whether you need an adjustment because the dates, when imported, will be approximately 60 years off from what you expect.

cynthia

[deleted_user]

I read several tables from one sheet. I didn't know that it can be done with the help of proc import. Now I know it and my program works.
Thanks.