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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.