SAS SQL Join

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:

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!!! 🤨