topic Re: Merge or join in SAS Programming

Merge or join

leahcho — Thu, 23 Nov 2017 15:45:38 GMT

Hi I need to merge two datasets but not sure if I can do it with data step.

My datasets look like this;

Dataset 1 Dataset 2

ID Var1 ID var2

1 a 2 a

2 b 5 b

3 c 3 c

4 d 4 d

When I merge these two datasets, I only want to keep all IDs from dataset 1 and add from dataset 2 if IDs match.

So I want a final dataset to look like this;

Data_merged

ID var1 var2

1 a

2 b a

3 c c

4 d d

Thanks much!

Re: Merge or join

PeterClemmensen — Thu, 23 Nov 2017 16:05:50 GMT

Like this?

data Dataset_1;
input ID$ Var1$;
datalines;
1 a
2 b
3 c
4 d
;

data Dataset_2;
input ID$ var2$;
datalines;
2 a
5 b
3 c
4 d
;

/* Data Step Approach */
proc sort data=Dataset_1; by ID; run;
proc sort data=Dataset_2; by ID; run;

data merged;
	merge Dataset_1(in=a) Dataset_2;
	by ID;
	if a;
run;

/* Proc SQL Approach */
proc sql;
	create table joined as
	select * 
	from Dataset_1 left join Dataset_2
	on Dataset_1.ID = Dataset_2.ID ;
quit;

Re: Merge or join

leahcho — Thu, 23 Nov 2017 16:23:46 GMT

Hi,

I tried

data want;

merge data1 (in=a) data2 (in=b);

by id;

if a;

run;

This is the same as what you suggested?

Thanks

Re: Merge or join

PeterClemmensen — Thu, 23 Nov 2017 20:36:22 GMT

Yes. Except you don't need your in=b in this case and you have to make sure that the two data sets are properly sorted.