topic Re: Failed to change character into date in SAS in SAS Procedures

Failed to change character into date in SAS

kiddcao — Wed, 14 Jan 2015 16:52:10 GMT

I imported 5 excel files into SAS and there are some dates formatted as 8/3/1989 originally and formatted into 03Aug1989 (DATE9.) which is what I really want. However, on 1 file the dates failed to converted into DATE9. and it is read as $CHAR10 when I read the log. I tried several ways to reformat it into DATE9 but failed.

I tried to change all informat/format/input into DATE9. instead of $CHAR10 but failed, the results are all empty (.)
I tried DateNew=input (Date,DATE9.); but it didn't work either.

Any comment?

Thanks!

Re: Failed to change character into date in SAS

Reeza — Wed, 14 Jan 2015 17:08:28 GMT

Your second method should be correct.

One thought is trailing spaces so perhaps a compress or trim might help?

DateNew=input (compress(Date),DATE9.);

Re: Failed to change character into date in SAS

ballardw — Wed, 14 Jan 2015 17:12:17 GMT

HOW did you get the data from Excel? Proc Import has a number of issues with Excel depending on what people put in the Excel file.

Also, look at the cell level formats in Excel. If they were set as character and the value entered as text '8/3/1989 they would likely come in as text.

If the values are 8/3/1989 then you want to use

DateNew = input(date, mmddyy10.);

and

Format DateNew date9.;

when use input(date,date9.) SAS expects the text value to look like 03Aug1989 not 8/3/1989.