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