When I import excel file into SAS dataset in EG 5.1, I got variables with date format error.
I use : Proc Import datafile= <excel file.xls>
out=<sas dataset>
dbms=xls replace;
getnames=yes;
All input fields are OK, only date fields have problems like that :
Excel format SAS read in SAS read in format
6/26/2014 41816 06/27/2074
10/8/2013 41555 10/09/2073
5/9/2011 40672 05/10/2071
........................................
SAS read in date fields which are off 60 years and 1 day in each value.
The option yearcutoff = 1920 in default.
Would you mind giving me a direction to resolve this problem.
Thanks in advance.
----------------------------
WT1968
It's a known problem. Take a look at: 41000 - Date values might be incorrect when you read Microsoft Excel files with the XLS engine
not sure if it could work. Did you try other engine ,like dbms=excel dbms=xlsx .
options datestyle=mdy;
It's a known problem. Take a look at: 41000 - Date values might be incorrect when you read Microsoft Excel files with the XLS engine
Thanks for all information. I appreciate your help to solve my problem quickly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.