Set vs Merge statements

[BrahmanandaRao]

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 

[PaigeMiller]

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?

[BrahmanandaRao]

Hi,

PaigeMiller

Using Merge with by variable second dataset overwrite first dataset 

why its happend 

[Kurt_Bremser]

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.

[PaigeMiller]

@BrahmanandaRao 

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

[Cynthia_sas]

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