Weird SAS date format after importing from Excel

[JibbyJoob]

Hello all.

I am importing a table with multiple date columns that end up importing to character columns, because these date columns also contain "N/A" values. Im wondering if theres a way to properly format these columns into dates, since the dates listed are just random numbers now...

Example: 

Closing_Date
44866
44866
44866
44866
44866
44866
44866
44866
44866
44866
44846
44866

I have tried the following code (found in the SAS community)

data work.Table2;
	set work.Table1;
Closing_Date = input(Date, 5.) + '30DEC1899'd;
format Closing_Date date9.;
run;

and I receive the following error:

24         
25         GOPTIONS ACCESSIBLE;
26         data work.Table2;
27         	set work.Table1;
28         Closing_Date = input(Date,5.) + '30DEC1899'd;
29         format Closing_Date date9.;
                                    ______
                                    484
NOTE 484-185: Format $DATE was not found or could not be loaded.

[Tom]

Fix your speadsheet so that all of the cells in that column have NUMBERS in them.  Then SAS will not be forced to convert the dates into character strings.

[Reeza]

For some reason closing_date is being considered a character. Do you have a variable in table1 already called closing_date?

[ballardw]

Format names that start with $ are for use  with CHARACTER values. When SAS gives a message like that then the variable is actually character. You cannot change the type of a variable once it is in a data set. So since apparently Closing_date was already character you did some fancy conversions that yield something, but not a numeric value that the DATE9. format can be applied to.

Try creating a new variable:

data work.Table2;
	set work.Table1;
NEW_Closing_Date = input(Date, 5.) + '30DEC1899'd;
format New_Closing_Date date9.;
run;

I suspect that you want - '30DEC1899'd.

Or avoid the whole problem to begin with. Make sure that all the "date" columns in the  spread sheet have a proper date appearance, do a File Save As to CSV from spreadsheet software and Import that CSV, which will have date appearing text that SAS should interpret correctly (or better yet: write your own data step to read that CSV).

Problems with importing data starting in spreadsheets is about the single most common question on this forum because spreadsheets impose no actual structure on data or restrictions on values in a column and hence make very lousy data interchange files.

One reason for the save to csv and read those with a data step is to get the same results for similar files. Proc Import guesses for each and every file what the characteristics of the variables are. So supposed identical "structured" source files can have variables with different lengths for character values (very common), change of variable types (not uncommon) and if your data source is really poor changing variable names as column headings get edited.

One of the typical causes of "character" values for your dates and other variables are the presence of multiple rows of "header" values in the spread  sheet. Import for spreadsheets assumes there is only one row of headers so the second row is treated as a value and hence those words make the column "character".