convert character sas date to number date

[Stalk]

I used proc import to read my data and one of the date field(4/13/24) imported as sas number date. How do I convert that date to date9. (12APR2024). Also in this field I have some character values like "No date" and some date values. How do I handle them.

data pm;

input exp $  Name $ ;

datalines;

45301 John        

44986 Jane      

45412 Bob     

No_date Emily 

10/5/24 Don 

;

run;

data temp_test;

  set pm;

  expiry=input(exp,date9.);

  run;

Thank you

[ballardw]

If the source of the file is a spreadsheet there are a couple of approaches:

First, you get character results because of the 'No_date' or whatever in the column. So one approach is to delete anything from the column that is not an actual date before import/reading the data.

Second, make sure all of the cells in the column have the same format. You may have people that entered a text date instead of the proper date. Fix them. Or entered numbers like 20210506 that they thought was a "date" but is not actually treated as such by either the spreadsheet or SAS.

Then "import" and see if things work better.

Personally I generally save spreadsheet files to a CSV file format an use a data step to read that. With the data step I set the approach used to read each column so can address some odd values.

Often the export to CSV will reduce the headaches associated with dates or at least create invalid data messages in the log so you are aware of them and can adjust the reading code.

[Tom · Accepted solution]

Why did you try to read the values using DATE9 informat?  None of the strings you showed look anything at all like the text that the DATE informat can read.

The values that look like numbers in the 40K range are probably Excel date values.  Convert them into numbers and adjust for the different way Excel and SAS number days.

Also what date do you want for that last value?  10/5/24 could be October Fifth of 1924 or 2024 (or 1824 for that matter).  But it could also be the Tenth of May for any of those years.  And it might be May 24th in the year 2010 (or 1910 or 1810).

data temp_test;
  set pm;
  expiry = input(exp,??32.);
  if not missing(expiry) then expiry=expiry+'30DEC1899'd ;
  else expiry=input(exp,mmddyy10.);
  format expiry date9.;
run;

[Stalk]

Thank you so much for all your time and help.

I think I found the issue. My excel has multiple sheets and I'm reading from one sheet where these dates values are referencing from another sheet. When I use import wizard the dates are imported correctly but when I use proc import the dates are importing in numeric format.

I have no control of changing the source file, updated by many hands.

But I'm in the process of scheduling a job to read from this excel and send email to users with the list of all the products that will expire this month

Excel date	SAS proc import date
1/10/2024	45301
11/5/2024	45601

proc import out=count_xlsx
datafile="c:temp\Inventory.xlsx"
dbms=xlsx
replace;
SHEET="count_Inventory";
getnames=YES;
run;