Here is my test data and program:
data dummy;
infile datalines;
length ID 4 Member $9.;
input ID Member $9.;
cards;
6666 Ben
3333 Christine
9999 Kelly
1111 Nick
5555 Josh
;;;;
run;
data dummy2;
infile datalines;
length ID 4 Member $9.;
input ID Member $9.;
cards;
3333 Jim
6666 Mike
1111 Luke
2222 Brian
5555 Jesus
;;;;
run;
proc sort data=dummy;
by ID;
run;
proc sort data=dummy2;
by ID;
run;
data test;
merge dummy(in=a) dummy2(in=b);
by ID;
if a and b;
run;
The test dataset only has observations from dummy2. Why is this? I want all observations from each where ID matches (I'm purposely trying to avoid PROC SQL).
Good solution.
As long as you have at most one observation per MEMBER in each data set, you could get the new result with:
data want;
set dummy dummy2;
by ID;
if first.id=0 or last.id=0 then output;
run;
If there's a possibility of a many-to-one match (or worse yet, many-to-many) it gets more complex.
Your final data set can only contain one variable named MEMBER? But you have conflicting values coming from both data sets. When you get MEMBER from the first data set, what would you like its variable name to be after the MERGE? When you get MEMBER from the second data set, what would you like its variable name to be after the MERGE?
Renaming like this essentially solves the problem:
data test;
merge dummy(in=a) dummy2(in=b rename=(Member=Memberr));
by ID;
if a and b then output;
run;
But what if I want to display it like this:
1111 Nick
1111 Luke
3333 Christine
3333 Jim
6666 Ben
6666 Mike
5555 Josh
5555 Jesus
or
1111 Luke
1111 Nick
3333 Jim
3333 Christine
6666 Mike
6666 Ben
5555 Jesus
5555 Josh
Good solution.
As long as you have at most one observation per MEMBER in each data set, you could get the new result with:
data want;
set dummy dummy2;
by ID;
if first.id=0 or last.id=0 then output;
run;
If there's a possibility of a many-to-one match (or worse yet, many-to-many) it gets more complex.
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.