SAS day 24: Proc 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:
Dataset A

Dataset B

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;

4. Join with conditionsSuppose 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!!! ????