topic Re: Proc sql Joins in SAS Programming

Proc sql Joins

Ranjeeta — Mon, 17 Jun 2019 18:02:27 GMT

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;

Re: Proc sql Joins

PaigeMiller — Mon, 17 Jun 2019 18:09:57 GMT

@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.

Re: Proc sql Joins

Ranjeeta — Mon, 17 Jun 2019 18:12:57 GMT

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

Re: Proc sql Joins

PaigeMiller — Mon, 17 Jun 2019 18:26:08 GMT

@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.

Re: Proc sql Joins

Ranjeeta — Mon, 17 Jun 2019 19:21:45 GMT

Please find attchd sample data

Re: Proc sql Joins

PaigeMiller — Mon, 17 Jun 2019 19:25:52 GMT

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-data-AKA-generate/ta-p/258712 and we also need to see the desired output as well.