SAS Changing Dates After Conversion from Character Variable

[leackell13]

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?

[PaigeMiller · Accepted solution]

they read as 21035, 25992, and 18554

the conversion from Excel requires this:

https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Conversion-from-Excel-Date-to-SAS...

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.

[leackell13]

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.

[SASKiwi]

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.

[leackell13]

Thanks for the suggestion, but I had tried this earlier, and it didn't work.