BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hello. Prompt, please. How to input date from excel if it is displayed there in an excel format?
7 REPLIES 7
Peter_C
Rhodochrosite | Level 12
should be automatic
by what method is your SAS reading excel?
deleted_user
Not applicable
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
Rhodochrosite | Level 12
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
Not applicable
don't work with informat anydtdte
SASKiwi
PROC Star
Curious to know why you are using DDE as compared to say PROC IMPORT where dates are handled automatically.
Cynthia_sas
SAS Super FREQ
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
Not applicable
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 795 views
  • 0 likes
  • 4 in conversation