BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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?

JediApprentice
Pyrite | Level 9

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
Astounding
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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