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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.