BookmarkSubscribeRSS Feed
rudfaden
Pyrite | Level 9

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

 

 

 

 

3 REPLIES 3
Tom
Super User Tom
Super User

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.;
MarkDawson
SAS Employee

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 !

Kurt_Bremser
Super User

<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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 540 views
  • 0 likes
  • 4 in conversation