topic Re: Proc sql to join three tables in SAS Programming

Proc sql to join three tables

Ranjeeta — Thu, 05 Mar 2020 19:49:07 GMT

Proc sql;
Create table NWHC_DAD as 
Select distinct
A.*
From NWHC_service as A
Left join DAD as D
On A.ID=D.ID and A.ID~=’9999999999’
Where D.fyear=2016 and A.days_to_servdate<=D.days_to_servdate+60
;quit;

Proc sql;
Create table NWHC_NRS as 
Select distinct
A.*
From NWHC_service as A
Left join NRS as N
On A.ID=N.ID and A.ID~=’9999999999’
Where D.fyear=2016 and A.days_to_servdate<=N.days_to_servdate+60
;quit;

I need to combine the three tables in one step so that I have a final table which has the IDs where the foll condition is satisfied along with the other conditions in the query : days_to_servdate from the A table is <= days_to servdate from table D or days_to servdate From table N

Please advise how would i do that

Re: Proc sql to join three tables

PaigeMiller — Thu, 05 Mar 2020 19:59:38 GMT

Joining three tables

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=sqlproc&docsetTarget=p0xbwe8mkbjx9zn1fkxw7skq9efd.htm&locale=en

Re: Proc sql to join three tables

SuryaKiran — Thu, 05 Mar 2020 20:22:50 GMT

Hello,

What join do you need? INNER or LEFT. For me it looks like you need an inner join for your requirement.

Proc sql;
Create table NWHC_DAD as 
Select distinct
A.*
From NWHC_service as A
inner join DAD as D On A.ID=D.ID and A.ID<>'9999999999'
inner join NRS as N On A.ID=N.ID and A.ID<>'9999999999'

Where D.fyear=2016 and ( A.days_to_servdate<=N.days_to_servdate or A.days_to_servdate<=D.days_to_servdate)
;quit;

Re: Proc sql to join three tables

Ranjeeta — Thu, 05 Mar 2020 21:12:07 GMT

proc sql;
create table NWHC_DAD_NRS as
select distinct 
A.* 
from NWHC_service as A
left join DAD as D
on A.ID=D.ID and A.ID~='999999999'
where A.days_to_servdate <=D.days_to_admdate+60 and fyear_ddate=2016
left join NRSdisrecs as N
on A.ID=D.ID and N.ID~='999999999'
where A.days_to_servdate <=N.days_to_admdate+60 and fyear_ddate=2016
group by id
order by id
;quit;

I wanted to do a left join to keep all records in A

Please advise how would i correc the query above to apply all the conditions