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;
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.
Look up the IN options for datasets.
You need to specify that you want records from both datasets.
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.
@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;
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.
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.