BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shixin
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
shixin
Calcite | Level 5

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

SASKiwi
PROC Star

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.

Tom
Super User Tom
Super User

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

departure_date < '01JAN1900'd

SASKiwi
PROC Star

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;

ballardw
Super User

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.

jakarman
Barite | Level 11

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 --<-----
LinusH
Tourmaline | Level 20

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
Amir
PROC Star

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.

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 10 replies
  • 5460 views
  • 3 likes
  • 8 in conversation