SAS Changing Dates After Conversion from Character Variable for Only Some Variables

[leackell13]

Hi,

I had previously posted about this issue, and for most of my variables, deleting the cells that weren't dates in the Excel file before importing the file and converting the variables worked:

date_varService = Input(Service_Date, mmddyy10.);
	format date_varService mmddyy10.;
	DROP Service_Date;
	RENAME date_varService=Service_Date;

However, there are 2 variables where that has not worked, and the other suggestions people have given have not worked. For one of the variables (Immediate_postop_XR_date), the 1st 3 dates are: 8/22/2016, 7/3/2019, and 1/15/2018. When I do the method above, I get 4/26/2004, ., and ., respectively.

21916 and 20454 are the 2 numbers that were suggested to convert Excel's calculation of dates to SAS's calculation of dates, but when I try this code:

XR_num = Input(Immediate_postop_XR_date, 8.);
	XR_right = XR_num - 21916;
	XR_char = Put(XR_right, $10.);
	date_varXR = Input(XR_char, mmddyy10.);
	format date_varXR mmddyy10.;
	DROP Immediate_postop_XR_date;
	RENAME date_varXR=Immediate_postop_XR_date;

I get 2/6/1988, 2/17/1933, and 2/11/1999.

And when I try this code:

XR_num = Input(Immediate_postop_XR_date, 8.);
	XR_right = XR_num - 20454;
	XR_char = Put(XR_right, $10.);
	date_varXR = Input(XR_char, mmddyy10.);
	format date_varXR mmddyy10.;
	DROP Immediate_postop_XR_date;
	RENAME date_varXR=Immediate_postop_XR_date;

 I get 2/21/1950, ., and 2/26/1961.

I'm using SAS Studio Release: 3.8 (Enterprise Edition)

Build date: Sep 1, 2020 11:12:26 AM
SAS release: 9.04.01M6P11072018
SAS platform: Linux LIN X64 3.10.0-1062.9.1.el7.x86_64
Site name: SAS ONDEMAND FOR ACADEMICS
Site number: 70094220

Would that make a difference?

While I don't see any non-dates or spaces in the variables I'm having trouble with, I've attached an Excel file with just those variables so you can see if I've missed anything.

Thanks.

[Reeza · Accepted solution]

XR_num = Input(Immediate_postop_XR_date, 8.);
date_xr = XR_num - 21916;
format date_varXR mmddyy10.;
	

If you're inputting it as 8. I assume you're seeing something like 21436? If so, something like above is likely what you need instead. 

Can you show a screenshot of the data in SAS and provide variable types?

---

@leackell13 wrote:

Hi,

I had previously posted about this issue, and for most of my variables, deleting the cells that weren't dates in the Excel file before importing the file and converting the variables worked:

date_varService = Input(Service_Date, mmddyy10.);
	format date_varService mmddyy10.;
	DROP Service_Date;
	RENAME date_varService=Service_Date;

However, there are 2 variables where that has not worked, and the other suggestions people have given have not worked. For one of the variables (Immediate_postop_XR_date), the 1st 3 dates are: 8/22/2016, 7/3/2019, and 1/15/2018. When I do the method above, I get 4/26/2004, ., and ., respectively.

21916 and 20454 are the 2 numbers that were suggested to convert Excel's calculation of dates to SAS's calculation of dates, but when I try this code:

XR_num = Input(Immediate_postop_XR_date, 8.);
	XR_right = XR_num - 21916;
	XR_char = Put(XR_right, $10.);
	date_varXR = Input(XR_char, mmddyy10.);
	format date_varXR mmddyy10.;
	DROP Immediate_postop_XR_date;
	RENAME date_varXR=Immediate_postop_XR_date;

I get 2/6/1988, 2/17/1933, and 2/11/1999.

And when I try this code:

XR_num = Input(Immediate_postop_XR_date, 8.);
	XR_right = XR_num - 20454;
	XR_char = Put(XR_right, $10.);
	date_varXR = Input(XR_char, mmddyy10.);
	format date_varXR mmddyy10.;
	DROP Immediate_postop_XR_date;
	RENAME date_varXR=Immediate_postop_XR_date;

 I get 2/21/1950, ., and 2/26/1961.

I'm using SAS Studio Release: 3.8 (Enterprise Edition)

Build date: Sep 1, 2020 11:12:26 AM
SAS release: 9.04.01M6P11072018
SAS platform: Linux LIN X64 3.10.0-1062.9.1.el7.x86_64
Site name: SAS ONDEMAND FOR ACADEMICS
Site number: 70094220

Would that make a difference?

While I don't see any non-dates or spaces in the variables I'm having trouble with, I've attached an Excel file with just those variables so you can see if I've missed anything.

Thanks.

---

[leackell13]

Thanks, that worked! I was making an extra step for myself.