Convert Character date value to numeric

[David_Billa]

In my program we've date values like 3/25/2022 and 04/24/2082 in two different fields from the excel. After executing the similar step as shown below in my program, it is producing the values like 03/26/2082 and 04/25/2082 in the result. Excepted result is same value as input (03/25/2022) but with SAS date format.

Excel which we're using was mess, so we've converted all the fields to character and then reading it from excel. I could not succeed with the below program if I tried to replicate the same step by passing the hardcoded value in the input function. Any help?

data have;	
	ORIG_STOP_SHIP = input('3/25/2022',best.);
	EXT_STOP_SHIP = input('4/24/2022',best.);
	format ORIG_STOP_SHIP EXT_STOP_SHIP  mmddyy10.;
run;

[Kurt_Bremser]

To convert a date string to a date, you must use a date informat. BEST. is for "normal" numbers and interprets digits, commas, periods, and scientific notation, but not dates or times.

data have;	
ORIG_STOP_SHIP = input('3/25/2022',mmddyy10.);
EXT_STOP_SHIP = input('4/24/2022',mmddyy10.);
format ORIG_STOP_SHIP EXT_STOP_SHIP mmddyy10.;
run;

Hint: for consistent readability of code, do not use tabs. Only use blanks for whitespace and indentation (except in DATALINES, when you want to use 09x as delimiter).

[PaigeMiller · Accepted solution]

As far as your conversion of character strings to SAS dates, you have to use the proper informat, which in this case is the MMDDYY informat.

data have;	
	ORIG_STOP_SHIP = input('3/25/2022',mmddyy10.);
	EXT_STOP_SHIP = input('4/24/2022',mmddyy10.);
	format ORIG_STOP_SHIP EXT_STOP_SHIP  mmddyy10.;
run;

In general, converting Excel dates to SAS dates involves subtracting about 60 years. You can use your favorite internet search engine to find the details.

[ballardw]

As an aside: dealing with dates from Excel you may want to consider investigating if the entire column is the same type before any "import" if reading directly from Excel. I have received data in Excel that mixes of Excel date values and strings. Which can behave poorly if "imported".