I imported an Excel file into SAS Studio. It read the date variables as character variables with a length of 5. For example, the first 3 data points of variable DOB are: 8/3/1957, 2/28/1971, and 10/18/1950. When I imported the file, they read as 21035, 25992, and 18554, respectively. So I changed the variable length and type:
DATA SubQ.dataclean2;
LENGTH DOB $10.;
SET SubQ.confounding;
format date_var mmddyy10.; /* Change dates from character to date values */
date_var = input(DOB,mmddyy10.);
DROP DOB;
RENAME date_var=DOB;
RUN;
This successfully changed the variable length and type, but those 3 same points are shown as 02/10/1935, ., and ., respectively. Why? Does it have to do with how the dates are written in the Excel file?
they read as 21035, 25992, and 18554
the conversion from Excel requires this:
So if you do that conversion, and then format the result as DATE9. (or whatever format you would like to use) you will get the correct date.
they read as 21035, 25992, and 18554
the conversion from Excel requires this:
So if you do that conversion, and then format the result as DATE9. (or whatever format you would like to use) you will get the correct date.
Hello,
Thank you for your help. I had some trouble implementing this solution, and I found it easier just to delete the "N/As" indicating missing values from the Excel file before importing it to SAS, but your solution might help someone with a similar issue.
Looks like SAS is not recognised DOB as a date to begin with. Try changing the column type in Excel to date and import it again.
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.