DATA Step, Macro, Functions and more

Inputing dates from excel.

Reply
N/A
Posts: 0

Inputing dates from excel.

Hello. Prompt, please. How to input date from excel if it is displayed there in an excel format?
Valued Guide
Posts: 2,175

Re: Inputing dates from excel.

should be automatic
by what method is your SAS reading excel?
N/A
Posts: 0

Re: Inputing dates from excel.

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?
Valued Guide
Posts: 2,175

Re: Inputing dates from excel.

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)
N/A
Posts: 0

Re: Inputing dates from excel.

don't work with informat anydtdte
Super User
Posts: 3,112

Re: Inputing dates from excel.

Curious to know why you are using DDE as compared to say PROC IMPORT where dates are handled automatically.
SAS Super FREQ
Posts: 8,743

Re: Inputing dates from excel.

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
N/A
Posts: 0

Re: Inputing dates from excel.

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