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!
How about just testing if it is really small? If you are doing this in SAS code then something like:
departure_date < '01JAN1900'd
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.
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...
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.
How about just testing if it is really small? If you are doing this in SAS code then something like:
departure_date < '01JAN1900'd
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;
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.
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?
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.
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.
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
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.