topic Merging Variables Between Datasets in New SAS User

Merging Variables Between Datasets

PatSB — Mon, 30 Aug 2021 17:42:19 GMT

Hello all,

New SAS User. I have two datasets (Dataset 1 and Dataset 2). The datasets contain ID variables (ID Var 1 & ID Var 2) that corresponds to individuals. Dataset 1 includes a variable (Var X) that contains test results. All individuals in Dataset 1 are contained in Dataset 2 but Dataset 2 has some additional individuals.

What I’m trying to do is: if the individual exists in both Datasets 1 and 2, I would like to set Var Y equal to the test value in Var X. Of note, the datasets are large with millions of observations.

I don't know where to start here so any help would be greatly appreciated.

Regards,

Pat

Dataset 1		Dataset 2
ID Var 1	Var X	ID Var 2	Var Y
1	1	1
2	1	2
3	0	3
4	0	4
5	0	5
6	2	6
7	2	7
8	2	8
9	0	9
		10
		11
		12
		13
		14
		15
		16

Re: Merging Variables Between Datasets

Reeza — Mon, 30 Aug 2021 17:48:23 GMT

*data sets need to be sorted before merging;
proc sort data=dataset1; by ID;run;
proc sort data=dataset2; by ID;run;

data want;
*specify data sets you want to merge;
*use IN option to identify records later;
merge dataset1 (in=d1) dataset2 (in=d2);

*specify what variables you will merge on;
by ID:

*checks if ID is in both data sets and then assigns as needed;
if d1 and d2 then varY=varX;

run;

Merging is covered in this section of the documentation.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p1pa3hnpchkgf7n1etensx665yxr.htm#n18yn6634syy8an14wgoch3orbd6

Re: Merging Variables Between Datasets

Kurt_Bremser — Tue, 31 Aug 2021 07:10:17 GMT

See this, data step with UPDATE:

data ds1;
input id x;
datalines;
1 1
2 2
3 3
;

data ds2;
input id y;
datalines;
1 .
2 .
3 .
4 .
;

data want;
update ds2 ds1;
by id;
run;

Please note how I used data steps with DATALINES to present example data. This makes it easy for everyone reading this to recreate the datasets with copy/paste and submit, without having to make guesses about variable attributes and content.