convert sas date to teradata date in proc sql where clause

mraza12 — Thu, 24 May 2018 17:07:52 GMT

I want to convert a sas date to Teradata date in the where clause below:

Sample code:

proc sql;

create table temp_4 as

select a.counter, max(c.ID) as CI_ID

from temp_3 a

left join table.abc b ON a.H_ID = b.X_ID

inner join table.def c ON b.CI_ID = c.CI_ID

left join table.ghi d ON c.CI_ID = d.CI_ID

where b.type ='HI' and substr(c.CI_LAST_NM,1,12) = a.Surname and substr(c.CI_FRST_NM,1,7) = a.First_Name

and c.CI_BIR_DT = a.DOB and c.CI_SEX_C = a.gen

group by a.counter

;

quit;

c.CI_BIR_DT = a.DOB CI_BIR_DT is a Teradata date and a.DOB is the sas date.

I tried a few options but failed to get the correct result. Please advise.

Many Thanks!

Re: convert sas date to teradata date in proc sql where clause

tomrvincent — Thu, 24 May 2018 17:34:06 GMT

Try formatting temp_3.DOB as format yymmdd10. before joining. That's worked before.