topic Re: Data Step Merge Results in SAS Programming

Data Step Merge Results

JediApprentice — Tue, 25 Apr 2017 20:50:32 GMT

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

Re: Data Step Merge Results

Astounding — Tue, 25 Apr 2017 20:55:40 GMT

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?

Re: Data Step Merge Results

JediApprentice — Tue, 25 Apr 2017 21:11:11 GMT

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

Re: Data Step Merge Results

Astounding — Tue, 25 Apr 2017 21:17:30 GMT

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.