BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
almmotamedi
Obsidian | Level 7

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
Reeza
Super User

Look up the IN options for datasets.

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

ballardw
Super User

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.

Reeza
Super User

@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;
almmotamedi
Obsidian | Level 7
Thank you

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 50034 views
  • 3 likes
  • 3 in conversation