DATA Step, Macro, Functions and more

Match Merging By Two Variables

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

Match Merging By Two Variables

[ Edited ]

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;

 

 


Accepted Solutions
Solution
‎02-19-2016 05:25 PM
Super User
Posts: 10,500

Re: Match Merging By Two Variables

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.

View solution in original post


All Replies
Super User
Posts: 17,819

Re: Match Merging By Two Variables

Look up the IN options for datasets.

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

Solution
‎02-19-2016 05:25 PM
Super User
Posts: 10,500

Re: Match Merging By Two Variables

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.

Super User
Posts: 17,819

Re: Match Merging By Two Variables

@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;
Contributor
Posts: 72

Re: Match Merging By Two Variables

Thank you
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 2914 views
  • 2 likes
  • 3 in conversation