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