DATA Step, Macro, Functions and more

Merging data sets one having multiple data lines

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Merging data sets one having multiple data lines

Hi,

I want to merge two datasets like "A" and "B" by a variable called ID. "A" has multiple data lines meaning multiple rows against same ID but "B" has single row against a ID. "A" not necessarily have all the ID as "B". When I tried merge statement, it appears with some extra rows in the output file. Like, "A" has 794 IDs and each ID has multiple data lines totalling 96246 rows. "B" has 1004 IDs and each ID has one corresponding row. When I merge, it appears with 96961 rows. How can I do it perfectly?

 

Cheers  


Accepted Solutions
Solution
‎09-10-2017 06:42 PM
Super User
Posts: 11,343

Re: Merging data sets one having multiple data lines

Are there other variables with the same name in both sets? What do you want to happen with those if there are? You can only have one variable with a given name in a result.

 

It is usually best to provide small examples of you input data set(s) and the code you used with those sets. Then describe what is in the output that you do not want or is missing.

Things as simple as the order of the data sets appearing on a merge statement in a data step make big differences in the output.

See this brief example:

data one;
   do x=1 to 3;
      do y = 4 to 6;
      output;
      end;
   end;
run;

data two;
   do x= 2 to 5;
      y= x+10;
      output;
   end;
run;

proc sort data=one;
   by x;
run;

proc sort data=two;
   by x;
run;

data mergeone;
   merge one two;
   by x;
run;

data mergetwo;
   merge two one;
   by x;
run;

Notice that the values for y are different for the two sets.

 

Merge with a by clause: when a value does not exist in both sets means that a record without a match is copied to the result.

So you are likely getting more records than you expect. You can use the data set IN= option to test which data set contributed the current observation and discard or keep.

data mergethree;
   merge two (in=InTWo)
         one (In=InOne);
   by x;
   if InTwo and InOne; /* keep records where only the X matches in both sets*/
run;
data mergefour;
   merge two (in=InTWo)
         one (In=InOne);
   by x;
   if InTwo ; /* keep records that come from two and the values from one when the match*/
run;

View solution in original post


All Replies
Super User
Posts: 19,815

Re: Merging data sets one having multiple data lines

Whars your definition of perfect? 

For joins I prefer SQL and I suspect a right or left join is what you're looking for here. 

New Contributor
Posts: 4

Re: Merging data sets one having multiple data lines

Thanks Reeza. Let me try. 

PROC Star
Posts: 1,760

Re: Merging data sets one having multiple data lines

For joins I generally prefer data steps as monitoring what goes in and out is easy.

With SQL, the log won't even mention how many rows were read. 

Solution
‎09-10-2017 06:42 PM
Super User
Posts: 11,343

Re: Merging data sets one having multiple data lines

Are there other variables with the same name in both sets? What do you want to happen with those if there are? You can only have one variable with a given name in a result.

 

It is usually best to provide small examples of you input data set(s) and the code you used with those sets. Then describe what is in the output that you do not want or is missing.

Things as simple as the order of the data sets appearing on a merge statement in a data step make big differences in the output.

See this brief example:

data one;
   do x=1 to 3;
      do y = 4 to 6;
      output;
      end;
   end;
run;

data two;
   do x= 2 to 5;
      y= x+10;
      output;
   end;
run;

proc sort data=one;
   by x;
run;

proc sort data=two;
   by x;
run;

data mergeone;
   merge one two;
   by x;
run;

data mergetwo;
   merge two one;
   by x;
run;

Notice that the values for y are different for the two sets.

 

Merge with a by clause: when a value does not exist in both sets means that a record without a match is copied to the result.

So you are likely getting more records than you expect. You can use the data set IN= option to test which data set contributed the current observation and discard or keep.

data mergethree;
   merge two (in=InTWo)
         one (In=InOne);
   by x;
   if InTwo and InOne; /* keep records where only the X matches in both sets*/
run;
data mergefour;
   merge two (in=InTWo)
         one (In=InOne);
   by x;
   if InTwo ; /* keep records that come from two and the values from one when the match*/
run;
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 110 views
  • 2 likes
  • 4 in conversation