cannot read in dates in this excel sheet

[rykwong]

Dear SAS community, really appreciate your help

I have this field study_date that has mixed formats and I am struggling with.  I imported this excel (first screenshot) then used this code but it generated many uninterpretable data.  Please see the second screenshot.  How can I read this column in to capture all the values as dates. Thanks again

datestudy=input(date_study, ANYDTDTE11.);

[Reeza]

I'm confused. Are there two different screenshots of the date_study column or one?

[rykwong]

Hi Reeza

this is first screenshot is how the data is in excel

this is the second screenshot is after the excel is read by sas

[Kurt_Bremser]

You have some incorrectly entered values in the Excel spreadsheet, which are considered as strings. Fix that.

[rykwong]

thanks

[Kurt_Bremser]

• Make sure you have consistent formatting in the spreadsheet
• Save to a text (csv) file
• Read it with a DATA step, and use the appropriate informat for the date column

[rykwong]

thanks

[ballardw]

Your first picture tells me that your "dates" are a mix of character and numeric values in Excel. Why do I think that you may ask. Value alignment: some are justified to the left of the cell, these are very likely character, the ones justified to the right are Excel date values with an Excel date display set (will typically have numeric values in the 44,000 range if roughly current which aren't treated as a date by any SAS informat because they do not have year, month and day components but instead are a number of days since 1 Jan 1900 or).

Easiest solution in the long wrong: If all the dates when you look at them in Excel have the same appearance then do a File Save As file to CSV. Then read that CSV file with a data step. If your column formatted properly you may be able to read with a known type informat like DATE9. and not deal with a guessing informat like the Anydtdte.

[rykwong]

thanks, will fix that

[Tom · Accepted solution]

So is the first one what EXCEL shows and the second is what you get if you run PROC IMPORT on the EXCEL file?

Then this is what happens when you MIX character and numeric values in the same COLUMN in the EXCEL worksheet.

Best solution is to change those string values like '02JUN2020'  (or is that 'O2JUN2O2O' instead) into actual DATE values.  Then the PROC IMPORT will make a NUMERIC variable.

If you have to fix the gibberish that PROC IMPORT generated then do something like:

data want;
  set have;
  date = input(date_study,??date9.);
  if missing(date) and not missing(date_study) then 
    date='30DEC1899'd + input(date_study,32.)
  ;
  format date date9.;
run;

So the first input will convert strings like '02JUN2020' into a number of days.  The second input will convert strings like '43843' into a number of days and then adjust for the difference in where SAS and EXCEL start numbering.

[rykwong]

this solve my problem!  thanks so much!