Hi,
I have two datasets with the same variables. Dataset 2 has some rows that are in Dataset 1, but also some rows that are unique to Dataset 2. What I'd like to do is stack the two datasets such that all rows from Dataset 1 remain, and the rows from Dataset 2 are only added if that row doesn't always exist in Dataset 1.
Dataset 1
ID categ_var mth_var
1 abc 200901
2 def 200901
Dataset 2
ID categ_var mth_var
1 abc 200901
1 abc 200902
Output dataset:
ID categ_var mth_var found_in_ds1
1 abc 200901 1
1 abc 200902 0
2 def 200901 1
Additionally, if possible, I would like to be able to create a variable indicating whether an ID from Dataset 2 was found in Dataset 1.
Any help is much appreciated.
Here's one way:
data Dataset1;
input ID categ_var $ mth_var ;
datalines;
1 abc 200901
2 def 200901
;
run;
data Dataset2;
input ID categ_var $ mth_var;
datalines;
1 abc 200901
1 abc 200902
;
data want;
merge
dataset1 (in=in1)
dataset2
;
by id categ_var mth_var;
found_in_ds1 = in1;
run;
You may need to either sort your data or use NOTSORTED option on the BY statement depending upon your goals.
Here is another way of stacking without duplicates.
data ds1;
input id categ_var $ mth_var;
cards;
1 abc 200901
2 def 200901
;
run;
data ds2;
input id categ_var $ mth_var;
cards;
1 abc 200901
1 abc 200902
;
run;
proc sql noprint;
create table want as
select * from ds1
union
select * from ds2;
quit;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.