topic Proc Import a Date Column in EXCEL as a Date in New SAS User

Proc Import a Date Column in EXCEL as a Date

InspectahDex — Wed, 20 Jan 2021 02:38:58 GMT

I imported an excel datasheet using proc import and one of the date columns came in as a character variable with numbers populated. Is there anyway to modify the proc import so that the date data comes in as it should? MM/DD/YYYY? I know this is happening because some values are stored as year only and some are blank.

Re: Proc Import a Date Column in EXCEL as a Date

Reeza — Wed, 20 Jan 2021 04:17:11 GMT

No, and it wouldn't correctly handle the cases where you have a year only.
If you can read it in from a text file you can control the format, but not Excel.

You can use INPUT to convert the dates that are full dates but you'll need to handle the years only or missing separately.

if length(date) = 10 then new_date = input(date, mmddyy10.);
else if length(date)=4 then new_date = mdy(7, 1, input(date, 4.));
else if missing(date) then new_date = .;

Re: Proc Import a Date Column in EXCEL as a Date

Tom — Wed, 20 Jan 2021 04:36:05 GMT

A variable only has one type, unlike a spreadsheet in a dataset you cannot mix numbers and character strings. If the column has some cells with dates (numbers) and some with strings then SAS will be forced to create the variable as character. When it does that the values of cells that contain date values are stored as the string of digits that represent the number Excel stores for that date. So the date 01JAN2021 will show up as the string '44197' instead of either '01JAN2021' or the number 22,281 that SAS would use to represent that date.

The best solution is to fix the column in the spreadsheet so that every value is a string. Then what you have in SAS will be the string.