topic Re: Merging data sets in New SAS User

Merging data sets

Lety08 — Thu, 29 Apr 2021 15:09:04 GMT

I am trying to merge 3 datasets that have independent IDs, but it only merge some observations instead of all of them

my code is as below and those datasets together should be above 4000 observations however, it only give me 2878 (as shown in the picture below from the log).

does anyone knows what am I doing wrong?

DATA AUCTION.TRUCKS;
 MERGE AUCTION.CHEVROLET (IN=A) auction.dodge (IN=B) auction.ford (IN=C) ;
 BY LI_SALE_NUMBER;
 IF A OR B OR C;
RUN;

Re: Merging data sets

Kurt_Bremser — Thu, 29 Apr 2021 15:16:27 GMT

You have matching values for LI_SALE_NUMBER in those datasets.

Are you sure you want a MERGE, and not stacking the datasets with a SET statement?

Re: Merging data sets

ballardw — Thu, 29 Apr 2021 15:24:51 GMT

Did you look at the output? Do the actual variables make sense?

MERGE in SAS is a side-by-side operation.

From the number of observations you say you expect then it appears you want to STACK the data which would be a SET not merge.

What you code is doing is aligning records based on the By variables. So if LI_SALE_NUMBER has the same value in two sets the record is combined into one.

Example of the difference between the MERGE and SET;

data two;
  input x z;
datalines;
1 111
3 333
4 444
;

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

data example2;
   set one two;
   by x;
run;

The result:

Example 1                                                           

x     y     z
1    11    111
2    22      .
3    33    333
4     .    444

Example 2                                                          

x     y     z
1    11      .
1     .    111
2    22      .
3    33      .
3     .    333
4     .    444