topic Re: Inputing dates from excel. in SAS Programming

Inputing dates from excel.

deleted_user — Tue, 01 Feb 2011 09:56:29 GMT

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

Re: Inputing dates from excel.

Peter_C — Tue, 01 Feb 2011 14:29:05 GMT

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

Re: Inputing dates from excel.

deleted_user — Tue, 01 Feb 2011 14:50:23 GMT

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?

Re: Inputing dates from excel.

Peter_C — Tue, 01 Feb 2011 15:04:22 GMT

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)

Re: Inputing dates from excel.

deleted_user — Wed, 02 Feb 2011 07:56:53 GMT

don't work with informat anydtdte

Re: Inputing dates from excel.

SASKiwi — Wed, 02 Feb 2011 21:12:23 GMT

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

Re: Inputing dates from excel.

Cynthia_sas — Wed, 02 Feb 2011 22:15:14 GMT

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

Re: Inputing dates from excel.

deleted_user — Thu, 03 Feb 2011 14:24:10 GMT

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.