topic Re: Merging data sets one having multiple data lines in SAS Programming

Merging data sets one having multiple data lines

strqimr — Wed, 06 Sep 2017 04:15:52 GMT

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

Re: Merging data sets one having multiple data lines

Reeza — Wed, 06 Sep 2017 05:05:38 GMT

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.

Re: Merging data sets one having multiple data lines

strqimr — Wed, 06 Sep 2017 05:09:09 GMT

Thanks Reeza. Let me try.

Re: Merging data sets one having multiple data lines

ChrisNZ — Wed, 06 Sep 2017 05:47:13 GMT

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.

Re: Merging data sets one having multiple data lines

ballardw — Wed, 06 Sep 2017 21:05:50 GMT

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;