Proc sql Joins

[Ranjeeta]

Hello,

I have 2 tables 

Reg_SHSC_sorted with 207 cases

CIHI_SHSC_sorted with 115 cases

I am trying to find the cases that are  present in the 1st table i.e. Reg_SHSC_sorted and missing from  the 2nd table i.e. CIHI_SHSC_sorted 

Is the following code correct?

proc sql;

create table MissingCIHI as

select a.RegID, a.days_to_removal_date,a.site,b.*

from reg_SHSC_sorted a

left join CIHI_SHSC_sorted b

on a.Reg_ID =b.ID

and a.site=b.site

and a.days_to_removal_date>=b.days_to_admdate-2

and a.days_to_removal_date <=b.days_to_ddate+2

where a.RegID NE b.ID

;quit;

[PaigeMiller]

---

@Ranjeeta wrote:

Hello,

I have 2 tables 

Reg_SHSC_sorted with 207 cases

CIHI_SHSC_sorted with 115 cases

I am trying to find the cases that are  present in the 1st table i.e. Reg_SHSC_sorted and missing from  the 2nd table i.e. CIHI_SHSC_sorted 

---

"Cases" meaning some ID value matches in both data sets, or "cases" meaning the entire record matches in both data sets? What do the dates in the code have to do with this problem, this is not explained anywhere.

[Ranjeeta]

I am interested in the Cases that have the same ID value and the days to removal date and the other dates mentioned are some of the join conditions that I want to be satisfied for cases that have the same ID value in both datasets

Thankyou 

[PaigeMiller]

---

@Ranjeeta wrote:

I am interested in the Cases that have the same ID value and the days to removal date and the other dates mentioned are some of the join conditions that I want to be satisfied for cases that have the same ID value in both datasets

---

This is still hard to decipher. Please show us some sample input data, and the desired output.

[Ranjeeta]

Please find attchd sample data

[PaigeMiller]

Many of us will not download and open MS Office documents as they are a security risk.

We need to see your data, preferably as a SAS data step, following these instructions https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... and we also need to see the desired output as well.