topic Re: Match Merging By Two Variables in SAS Programming

Match Merging By Two Variables

almmotamedi — Fri, 19 Feb 2016 21:51:18 GMT

Hi SAS users,

I am trying to match merge two data set having two common variables as below (here is a small portion of the dataset for simplicity):

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

Dataset1:

variable1 variable2

a 10

a 15

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

Dataset2:

variable1 variable2

a 15

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

After merging, my output is like this:

Merged_Dataset:

variable1 variable2

a 10

a 15

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

However, I expect the final output to be like below:

Desired_Dataset:

variable1 variable2

a 15

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

data Merged_Dataset;
merge Dataset1 Dataset2 ;
by Variable1 Variable2;
run;

Re: Match Merging By Two Variables

Reeza — Fri, 19 Feb 2016 22:12:42 GMT

Look up the IN options for datasets.

You need to specify that you want records from both datasets.

Re: Match Merging By Two Variables

ballardw — Fri, 19 Feb 2016 22:12:59 GMT

You just missed a minor option:

data  Merged_Dataset;
   merge Dataset1 
         Dataset2 (in = in2);
   by Variable1 Variable2;
   if in2;
run;

The dataset option IN sets a temporary variable as named (in2 in my example) to 1 when the dataset contributes any part of the observation. The IF then only keeps values when that variable is 1 (or True).

You can extend this quite easily to do calculations based on contributions from one or both data sets.

Another option is Proc SQL and a Right or Left Join.

Re: Match Merging By Two Variables

Reeza — Fri, 19 Feb 2016 22:22:34 GMT

@ballardw Code assumes all records in table 2. If your criteria is records in both tables it would be:

data  Merged_Dataset;
   merge Dataset1 (in=in1)
         Dataset2 (in = in2);
   by Variable1 Variable2;
   if in1 and in2;
run;

Re: Match Merging By Two Variables

almmotamedi — Fri, 19 Feb 2016 22:29:49 GMT

Thank you