topic Re: How to read 1/1/0001 date? in SAS Procedures

How to read 1/1/0001 date?

shixin — Tue, 23 Sep 2014 19:40:56 GMT

Hi SAS experts,

I am importing a file from teradata and I want to KEEP records with date 1/1/0001. (it appears as . in SAS)

It's basically a list of members with their departure dates. If a member is still alive, their departure_date will be 1/1/0001 in teradata (and . in SAS)

I tried the following ways but to no avail:

departure_date = .

departure_date is null

departure_date = '01-01-0001'

departure_date = mdy(1,1,0001)

departure_date = '01JAN0001'

departure_date = '0001-01-01'

Are there any others I missed?

Thanks in advanced!

Re: How to read 1/1/0001 date?

PaigeMiller — Tue, 23 Sep 2014 19:51:57 GMT

I am struggling to understand what the problem is here.

If a record has a date of . then it should be easy to identify and keep.

Re: How to read 1/1/0001 date?

SASKiwi — Tue, 23 Sep 2014 19:55:12 GMT

I suggest you try SQL passthru. That way you can reference the Teradata date explicitly.

proc sql;

connect to teradata (user=myusr1 pass=mypwd1 mode=teradata);

create MyTable as

select * from connection to teradata

(select *

from MyTeradataTable

where departure_date = '1/1/0001'

);

disconnect from teradata;

quit;

Re: How to read 1/1/0001 date?

shixin — Tue, 23 Sep 2014 19:59:09 GMT

It appears . in SAS because 1/1/0001 doesnt exists (3/3/2014 will appear as 3-3-2014 in SAS)

But when I use the filter departure_date = . it generated 0 rows.

Not sure if I made myself clear.. hmm...

Re: How to read 1/1/0001 date?

SASKiwi — Tue, 23 Sep 2014 20:12:27 GMT

How are you getting your data from Teradata to SAS? I assumed you were using SQL to query Teradata directly. If this is not the case please explain how you are doing it.

Re: How to read 1/1/0001 date?

Tom — Tue, 23 Sep 2014 20:20:03 GMT

How about just testing if it is really small? If you are doing this in SAS code then something like:

departure_date < '01JAN1900'd

Re: How to read 1/1/0001 date?

ballardw — Tue, 23 Sep 2014 21:49:11 GMT

SAS date values aren't defined before around the year 1581 AD or 1582 IIRC due to changes in calendars.

What data do you have that you think you actually have something measured on January 1, 1 AD.? Or are you getting some default code from the source that really means, "we don't have a value but the database requires something at this is it", in which case SAS missing is quite appropriate.

Re: How to read 1/1/0001 date?

jakarman — Wed, 24 Sep 2014 11:04:02 GMT

the Gregorian calendar may not be seen valid as of year 1581 (or orthodox church today). Calendar system always are changing.

The mathematic numbering of days sas is using internally however will be correct. What is the goal?

Re: How to read 1/1/0001 date?

LinusH — Wed, 24 Sep 2014 11:41:58 GMT

As stated by other, SQL pass-thru is the only feasible option here.

If you must perform this in SAS, have a view created in TD where the data column is casted to varchar. Could affect performance though.

Re: How to read 1/1/0001 date?

Amir — Wed, 24 Sep 2014 12:20:23 GMT

Hi,

Further to the other suggestions, it might be worth confirming you can select data based on a more current date that you know exists. This should help confirm that there are no other issues at play here.

Regards,

Amir.

Re: How to read 1/1/0001 date?

jakarman — Wed, 24 Sep 2014 13:22:18 GMT

Teradata gets filled from an other system. A data like this doe not make sense when it should be from something of these centuries.

Looks to be a data quality issue.

For Teradata look at: https://support.sas.com/resources/papers/teradata.pdf

with the datatypes chapter this conversion problem is described in the access interface. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition