09-23-2014 03:40 PM
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!
09-23-2014 03:59 PM
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...
09-23-2014 04:12 PM
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.
09-23-2014 03:55 PM
I suggest you try SQL passthru. That way you can reference the Teradata date explicitly.
connect to teradata (user=myusr1 pass=mypwd1 mode=teradata);
create MyTable as
select * from connection to teradata
where departure_date = '1/1/0001'
disconnect from teradata;
09-23-2014 05:49 PM
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.
09-24-2014 07:04 AM
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?
09-24-2014 07:41 AM
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.
09-24-2014 08:20 AM
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.
09-24-2014 09:22 AM
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
Need further help from the community? Please ask a new question.