topic Re: Input Function for Date in SAS Programming

Input Function for Date

margnotis — Thu, 30 Mar 2023 12:54:55 GMT

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;

Re: Input Function for Date

Kurt_Bremser — Thu, 30 Mar 2023 12:58:39 GMT

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.

Re: Input Function for Date

Tom — Thu, 30 Mar 2023 17:53:24 GMT

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;

Re: Input Function for Date

Quentin — Thu, 30 Mar 2023 16:31:02 GMT

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