topic Re: Match merge by multiple variables in SAS Programming

Match merge by multiple variables

bebaioun — Tue, 07 May 2019 01:25:34 GMT

Hi,

I am trying to match merge two large data sets having nine common variables (v1-v9) with 11 variables in total (random values put in as example):

--------------------------------------------------

Dataset1:

v1 v2 v3 v4 v5 v6 v7 v8 v9 v_a v_b

1 10 1 24 5 12 15 21 3 11 12

1 15 1 27 9 13 17 21 1 11 12

--------------------------------------------------

Dataset2:

v1 v2 v3 v4 v5 v6 v7 v8 v9 v_c v_d

a 10 1 24 5 12 15 21 3 11 12

a 15 1 27 9 13 17 21 1 11 12

--------------------------------------------------

After merging, I would like my output in the format below:

Merged_Dataset:

v1 v2 v3 v4 v5 v6 v7 v8 v9 v_a v_b v_c v_d

-------------------------------------------------------

Could you please help?, the code I used is as below:

data Merged_Dataset;

merge Dataset1

      Dataset2 (in = in2);

      by v1 v2 v3 v4 v5 v6 v7 v8 v9 ;

      if in2;

run;

I would like to see if there are any duplicates in the observations that have common values for the 9 variables that are of interest. But I am not sure how to do this after matching.

Any valuable feedback would be appreciated. Thanks!

Re: Match merge by multiple variables

ScottBass — Tue, 07 May 2019 02:17:49 GMT

Edit your original post with self-contained data steps. Don't make us do your work by forcing us to convert your post into usable code.

Re: Match merge by multiple variables

PGStats — Tue, 07 May 2019 03:27:28 GMT

For the merge operation to work, you need:

variables v1-v9 to be of matching types in both datasets and
both datasets to be sorted by v1-v9.

Once you meet these conditions, you can detect key duplicates as you merge the datasets with:

data Merged_Dataset;

merge Dataset1 Dataset2;
by v1 v2 v3 v4 v5 v6 v7 v8 v9;

if not (first.v9 and last.v9) then put "Duplicate" _all_;
run;

(untested)

Re: Match merge by multiple variables

bebaioun — Tue, 07 May 2019 03:32:43 GMT

Oh sorry, it is my first time posting (as I am a new SAS user)!

Will work on the format.

Re: Match merge by multiple variables

bebaioun — Tue, 07 May 2019 03:36:03 GMT

Thanks so much! I will try it on the data.

Really appreciate your feedback!