I am trying to import data using proc import and having trouble with a date format. In Excel the date appears as MM/DD/YYYY but when I run the import, I get a 5-character string that does *not* correspond to the SAS date (e.g. the date is 10/08/2019, and after the import the cell shows "43745", which, even if I convert it to a date, is equivalent to 10/08/2079).
Is there a way I can specify in the proc import how I want to import this particular column?
proc import out=data_collected datafile="SchoolList.xlsx" DBMS=XLSX replace ; range="'School_Data'$A:K"; run;
The date you are getting is an excel representation of the date which starts from 1 under 1JAN1900. Even though they are displayed as dates excel is storing them as numbers underneath. You can fix your excel sheet not to do that by playing around with date formats, or subtract 21916, from the number you get after reading into sas, and format the number as a date.
The date you are getting is an excel representation of the date which starts from 1 under 1JAN1900. Even though they are displayed as dates excel is storing them as numbers underneath. You can fix your excel sheet not to do that by playing around with date formats, or subtract 21916, from the number you get after reading into sas, and format the number as a date.
This makes sense. The Excel sheet is fixed but I can subtract 21916 when I do some other data cleanup. Thank you!
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.