Input Function for Date

[margnotis]

Hi I'm back again with some more date questions since I can't seem to ever do anything right with them.

So I have a field that is a mixture of dates and other information that im importing from excel. I'm sorting it by a case statement then doing a data step to reformat it and I'm just getting back nulls when I try and turn this characters into dates.

Code:

proc sql;
create table test as
select *
, case when length(field1) <= 5 then field1 end as new_date_field
from work.name;
run;

data test2;
set test1;
format var1 date9.;
var1= input(new_date_field,yymmdd12.);
run;

[Kurt_Bremser]

I moved your question to its own thread. Please do not hijack other's threads for your questions.

Your CASE will populate new_date_field only when theres less than 6 characters in field1, but then you try to use an informat with a width of 12. That won't work.

[Tom]

If you have COLUMN in an Excel spreadsheet that is "a mixture of dates and other information" and that means both numbers and character strings then SAS will have to create a character VARIABLE to store the information it reads from that column.

When it does that it converts the NUMBERS that Excel uses to store dates as a digit string.

So to convert those digit strings into a SAS date value you will need to first convert them into a number and then modify the number to account for the difference in how Excel and SAS number dates.

data test;
  set name;
  if lengthn(field1) in (1:5) then new_date_field=input(field1,??5.)+'30DEC1899'd;
  format new_date_field yymmdd10.;
run;

[Quentin]

30Dec1899 ?  

Gotta love the persistence of old bugs.  And Microsoft doesn't even call it a leap year bug, they just say they designed Excel to be compatible with Lotus 1-2-3...

https://learn.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year