BookmarkSubscribeRSS Feed
Walternate
Obsidian | Level 7

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. 

2 REPLIES 2
ballardw
Super User

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.

 

SAS_inquisitive
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 792 views
  • 0 likes
  • 3 in conversation