I have to dates. one is january 1. 0000 and the other is january 1. 2001. But sas reads them both as january 1. 2001. How can i get january 1. 0000 to be 0 or missing.
data test;
dt1 = input('0001-01-01T00:00:00Z', anydtdtm.);
dt2 = input('2001-01-01T00:00:00Z', anydtdtm.);
format dt1 dt2 IS8601DT.;
run;
I get this
dt1 dt2 2001-01-01T00:00:00 2001-01-01T00:00:00
But I wan't this
dt1 dt2
0001-01-01T00:00:00 2001-01-01T00:00:00
or this
dt1 dt2 1960-01-01T00:00:00 2001-01-01T00:00:00
SAS dates do not go that far back in time.
If you want to set a lower limit of 1960 then do that by testing the original string.
string = '0001-01-01T00:00:00Z';
if string < '1960' then dt=0;
else dt = input('0001-01-01T00:00:00Z', anydtdtm.);
format dt datetime20.;
The explanation for the behaviour of year 0001 being changed to 2001 is in SAS Usage Note 7330: Date informats read values of 0000-0099 as two-digit years
As already suggested by @rudfaden, the only workaround is to check if the year is going to be valid; i.e. 1582 or later.
However, as ANYDTDTM ANYDTDTM Informat can read dates with the year in different places, that might be tricky to achieve !
<rant>
Do NOT use the "any" informats. They will try their "best" to make dates or times out of all kinds of crap that has no right to exist in the first place, with (at best) hilarious results.
</rant>
Having said that, you get very nice timestamps in ISO 8601 format, so you positively should use the E8601DT informat.
Since SAS won't process dates before 1582 (the introduction of the Gregorian calendar), you only need to create a mechanism for date(time)s before that. With DB/2, 0001-01-01 is the default value for date columns that can't be missing. When we import such dates into SAS, we set them to missing. Other dates might have special meanings as defined in your organization. We have special timestamps in the years 900-999 that signal data that was migrated from an old system; for SAS, we replace the "09" at the beginning with "17", as we can be sure none of our customers is that old, or will have a claim from back then 😉
So you best read your values as character first, make the necessary adjustments, and then convert to a SAS datetime with the INPUT function and the E8601DT informat, or set a missing value explicitly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.