SAS SQL Join

Review:

Last time we went to over SAS Merge, it is a SAS Merge statement used for  1 – 1 mapping or One – Many mapping,

What should we do for many to many mapping?

Problem:

Suppose we want to generate a dataset which has the combined info from both dataset A and B.
Note: Data A and B both have more than 1 record for each patient.

Sample Dummy Dataset:

Solutions:

1. One-sided Join( Left join or Right join)
Suppose we want to join dataset A to all the records in dataset B
Keywords: right join / Left join

SAS Code:

```proc sql noprint nowarn;
create table example as
select distinct b.*,  a.pt, a.transyn
from a right join  b
on a.pt=b.pt
;
quit;```

2. Intersection (Inner Join)

Suppose we want to produce all the records that contained in both Dataset A and Dataset B
Keywords:  inner join

SAS Code

```proc sql noprint nowarn;
create table example as
select distinct b.*,  a.pt, a.transyn
from a inner join  b
on a.pt=b.pt
;
quit;```

3. Union (full Join)

Suppose we want to generate a dataset that contains either dataset A or dataset B
Keywords: full join

```proc sql noprint nowarn;
create table example as
select distinct b.*,  a.pt, a.transyn
from a full join  b
on a.pt=b.pt
;
quit;

proc sql noprint nowarn;```

4. Join with conditions

Suppose we want to select all the records with Transyn=”Yes”
Keywords: where

SAS Code:

```proc sql noprint nowarn;
create table exam_inner as
select distinct b.*,  a.pt, a.transyn
from a inner join  b
on a.pt=b.pt
where transyn="Yes"
;
quit;```

Summary:

A lot of times we need to combine the info from two datasets or more, in order to amalgamate the info efficiently,
we use SAS Merge for 1 – 1 or 1- many mapping with at least one common key variables,
and use Proc SQL to generate the datasets with many to many mappings.

Happy studying!!! 🤨

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Social media & sharing icons powered by UltimatelySocial