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

Hi,

I'm not getting any matches when I try to join 2 tables together on a date field when using SAS EG 4.3.

One table was created in an Oracle DB & the other in Teradata SQL Assistant 13.11.

Also, the Oracle date field is in a Date/Time format ('DDMONYYYY:HH:MM:SS') & the Teradata date field is in date format ('DDMONYYYY')

I've tried changing the format of the Oracle date/time format to just date 'DDMONYYYY' in order to match the Teradata table but I still get no matches.

I've checked both tables & there are matching values in both in the 'DDMONYYYY' format.

Can anyone advise what i should try to get these values to match?

Thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Convert into true SAS dates, by creating new calculated variables in EG and join on the calculated variables.

View solution in original post

10 REPLIES 10
Haikuo
Onyx | Level 15

Use datepart(your oracle datetime variable) to match Teradata.

Haikuo

pcfriendly
Calcite | Level 5

Thanks Hai.kuo, but I don't know what you mean.

Can you elaborate with maybe an example?

Thanks

Haikuo
Onyx | Level 15

Ok, the date variable straight from Oracle will be taken as date time format by SAS. So you need to truncate it into date before you can join with other date. if you are writing BASE code or using a Store Process in EG, then you can apply datepart() function to your Oracle date variable in your code before the join. However, You could be using Query builder, in that case, you need to "add" a computed variable using datepart(), then use this new variable to do the join.

HTH,

Haikuo

pcfriendly
Calcite | Level 5

Thanks again,

You're right, I am using the query builder & I've used the datepart function as datepart(Fieldname) which I tested on one value that I know matches.

Only thing is that it has changed the date to 19521 which I understand to be the number of days since 1st Jan1960. This isn't going to match a date format in my other table though ??

What do I do from here?

Reeza
Super User

If its a date it may, if it's a char you can do a similar conversion, using datepart, or input/put to create a sas date and join.

Basically you need to make the two dates look the same, so you create two new variables that have the same type. I'm not familiar with Teradata so I can't explain how to do it for that type of data.

pcfriendly
Calcite | Level 5

Thanks Reeza,

I've already tried changing the values to match as I stated at the start.

"I've tried changing the format of the Oracle date/time format to just date 'DDMONYYYY' in order to match the Teradata table but I still get no matches."

I tried applying a Format to both fields such as 'DDMMYYN8.' but still no matches even though the values look identical on the tables??

Reeza
Super User

I don't think SQL merges on formatted values, but on the underlying values.

pcfriendly
Calcite | Level 5

So, if that's the case, what do I need to do to fix this?

Reeza
Super User

Convert into true SAS dates, by creating new calculated variables in EG and join on the calculated variables.

pcfriendly
Calcite | Level 5

Thanks

After a lot of testing the functions, I used DATDIF to manually subtract the date field value from 1st Jan 1960 which gave me a number, I could then match these number fields.

I think that's what you were trying to tell me Reeza.

This is a lot of messing about to sort such a simple issue out???

I thought I'd just have to truncate the HH:MM:SS off the date Time field & I'd get matches, but no ... 

I used datepart to change the date from a normal date format to a number on the date/time field & then create a number from a date using datedif for the other table.

 

DATDIF('01jan60'd, my_date_field ,'act/act').

Thanks to both of you - surely there's an easier way ???

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 2711 views
  • 3 likes
  • 3 in conversation