topic Re: SQL merge to SAS merge (Proc SQL) in SAS Programming

SQL merge to SAS merge (Proc SQL)

PetePatel — Wed, 20 May 2020 15:57:44 GMT

Hi,

How do I convert the following code extract from SQL to SAS?

I don't believe datediff works in SAS.

LEFT JOIN pi_table as pi
ON lo.id=pi.id
AND DATEDIFF(dd,pi.DateStart,ld.TDate)>=0
AND DATEDIFF(dd,ld.TDate,ISNULL(pi.DateEnd,ld.TDate))>=0

Thanks

Re: SQL merge to SAS merge (Proc SQL)

Reeza — Wed, 20 May 2020 16:13:59 GMT

INTCK() would be an equivalent function.

Re: SQL merge to SAS merge (Proc SQL)

PetePatel — Wed, 20 May 2020 16:24:50 GMT

Thanks Reeza.

For the first part, I tried :
INCK('day',pi.DateStart,ld.TDate)>=0

How would I code the second part which tried to use tdate when DateEnd is not populated.

Re: SQL merge to SAS merge (Proc SQL)

Reeza — Wed, 20 May 2020 16:35:42 GMT

What does ISNULL() do in your second filter? You have to convert all your functions to SAS functions or explicit SQL Pass through.