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
Convert into true SAS dates, by creating new calculated variables in EG and join on the calculated variables.
Use datepart(your oracle datetime variable) to match Teradata.
Haikuo
Thanks Hai.kuo, but I don't know what you mean.
Can you elaborate with maybe an example?
Thanks
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
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?
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.
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??
I don't think SQL merges on formatted values, but on the underlying values.
So, if that's the case, what do I need to do to fix this?
Convert into true SAS dates, by creating new calculated variables in EG and join on the calculated variables.
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 ???
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!
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.