topic Re: SAS Changing Dates After Conversion from Character Variable in SAS Programming

SAS Changing Dates After Conversion from Character Variable

leackell13 — Thu, 18 Nov 2021 21:45:52 GMT

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?

Re: SAS Changing Dates After Conversion from Character Variable

PaigeMiller — Thu, 18 Nov 2021 22:26:40 GMT

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-Date/td-p/475824

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.

Re: SAS Changing Dates After Conversion from Character Variable

SASKiwi — Fri, 19 Nov 2021 01:19:02 GMT

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.

Re: SAS Changing Dates After Conversion from Character Variable

leackell13 — Fri, 19 Nov 2021 15:17:20 GMT

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

Re: SAS Changing Dates After Conversion from Character Variable

leackell13 — Mon, 22 Nov 2021 20:33:43 GMT

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.