topic Re: Set vs Merge statements in SAS Programming

Set vs Merge statements

BrahmanandaRao — Thu, 17 Sep 2020 11:26:20 GMT

data m2m;
infile datalines;
input id$ x;
cards;
A25 24
A25 22
A25 76
;
run;

data m2m1;
infile datalines;
input id$ x;
cards;
A25 24
A25 22
A25 6
;
run;

data m2m3;
merge m2m m2m1;
proc print;
run;/*with out by variable*/

proc sort data =m2m1;
by id;
run;

proc sort data=m2m1;
by id;
run;


data m2m3;
merge m2m m2m1;
by id;
proc print;
run;/*with by variable*/

proc sort data =m2m1;
by id;
run;

proc sort data=m2m1;
by id;
run;

data s2s;
set m2m m2m1;
by id;
proc print;
run;

Here i using set and Merge statements in set with by variable all obeservations came but merge with by variable second dataset over write in first dataset

Re: Set vs Merge statements

PaigeMiller — Thu, 17 Sep 2020 11:35:41 GMT

Here i using set and Merge statements in set with by variable all obeservations came but merge with by variable second dataset over write in first dataset

Yes, this is expected when you merge data sets with the exact same variable name in both data sets. What is your question?

Re: Set vs Merge statements

BrahmanandaRao — Thu, 17 Sep 2020 12:11:57 GMT

Hi,

PaigeMiller

Using Merge with by variable second dataset overwrite first dataset

why its happend

Re: Set vs Merge statements

Kurt_Bremser — Thu, 17 Sep 2020 12:32:42 GMT

Since all variables appear in both datasets, the PDV (Program Data Vector) will have two variables, id and x.

Since both datasets contain an equal number of observations with identical ud variables, both the MERGE with and without BY will have the same results:

an observation from m2m is read, and its contents put into the PDV
an observation from m2m1 is read, its contents overwriting the values in the PDV
these values are written to the output

When SET with a BY is used, the data step will interleave the datasets, it reads one observation for an id value from m2m, writes it to the output, reads an observation for the same id from m2m1, and writes that. This is why you have 3 observations after the MERGE, but 6 after the SET.

Re: Set vs Merge statements

PaigeMiller — Thu, 17 Sep 2020 13:34:34 GMT

@BrahmanandaRao

What result would you like to see instead of overwriting the data?

Re: Set vs Merge statements

Cynthia_sas — Thu, 17 Sep 2020 18:01:36 GMT

Hi:

Here are some slides I did in a presentation to illustrate SET vs MERGE

SET:

Simple MERGE

all possible MERGE outputs:

I recommend nearly ALWAYS using a BY and IN= with MERGE to make sure you have complete control over the MERGE output.

Cynthia