DATA Step, Macro, Functions and more

Data Step Merge Results

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Data Step Merge Results

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).


Accepted Solutions
Solution
‎04-25-2017 05:23 PM
Super User
Posts: 5,497

Re: Data Step Merge Results

Posted in reply to JediApprentice

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.

View solution in original post


All Replies
Super User
Posts: 5,497

Re: Data Step Merge Results

Posted in reply to JediApprentice

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?

Frequent Contributor
Posts: 123

Re: Data Step Merge Results

Posted in reply to Astounding

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
Solution
‎04-25-2017 05:23 PM
Super User
Posts: 5,497

Re: Data Step Merge Results

Posted in reply to JediApprentice

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.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 123 views
  • 0 likes
  • 2 in conversation