Help using Base SAS procedures

How to read 1/1/0001 date?

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How to read 1/1/0001 date?

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!


Accepted Solutions
Solution
‎09-23-2014 04:20 PM
Super User
Super User
Posts: 7,060

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

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

departure_date < '01JAN1900'd

View solution in original post


All Replies
Trusted Advisor
Posts: 1,929

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

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.

Contributor
Posts: 22

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

Posted in reply to PaigeMiller

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

Super User
Posts: 3,256

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

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.

Solution
‎09-23-2014 04:20 PM
Super User
Super User
Posts: 7,060

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

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

departure_date < '01JAN1900'd

Super User
Posts: 3,256

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

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;

Super User
Posts: 11,343

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

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.

Trusted Advisor
Posts: 3,214

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

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?

---->-- ja karman --<-----
Super User
Posts: 5,431

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

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.

Data never sleeps
Super Contributor
Posts: 282

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

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.

Trusted Advisor
Posts: 3,214

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

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 

---->-- ja karman --<-----
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1286 views
  • 3 likes
  • 8 in conversation