one-to-many match

[dapenDaniel]

Hi,

I have two datasets as below.

data have1;
input ID $ Var1;
datalines;
AA 0
AA 0 
BB 0  
run;

data have2;
input ID $ Var1;
datalines;
AA 1
run;

I would like to replace all id=AA in have1 with the value of id=AA in have2. The expected results are below.

ID       Var1

AA        1

AA        1

BB        0

I used the following program but it can only replace the value of first AA with 1 and that of the second one is still 0

proc sort data=have1 out=have11;
by classD;
run;

proc sort data=have2 out=have21;
by classD;
run;

data merged;
merge have11 (in=q) have21;
by id;
if q;
run;

How can I get the expected results? Thanks.

[novinosrin · Accepted solution]

proc sort data=have1 out=have11;
by id;
run;

proc sort data=have2 out=have21;
by id;
run;

data merged;
merge have11(in=q) have21(in=z);
by id;
if q;
var1=z;
run;

[dapenDaniel]

Thank you @novinosrin 

[dapenDaniel]

Hi @novinosrin 

actually, I have 335 variables after Id and their name is Col2, Col3,...Col336. How can I let SAS know that I would like to keep the value of id=AA in have2? Thanks.

[novinosrin]

Well your right table i.e the variable names in table name specified on the right will overwrite the values in the left if the names the the same. Is this your concern?

In any case, test for like 10-15 vars manually and then you are ought to be more confident 

[dapenDaniel]

Sorry, I did not put it in a clear way. I mean there are 335 variables called Var1, Var2,...Var336 after Id. I have added 5 variables manually and it works (code is below). I am wondering whether there is a way that I can tell SAS to overwrite the value for all these 335 variables, like Var2-Var336=z (I tried it but it did not work).

data merged;
merge have11 (in=q) have21 (in=z);
by id;
if q;
Var58=z;
Var65=z;
Var88=z;
Var144=z;
Var203=z;
run;

[novinosrin]

Ah ok, So are the Vars consecutive in sequence?

If yes,

array t Var2-Var336; grouping can work

data merged;
merge have11 (in=q) have21 (in=z);
by id;
if q;
array t Var2-Var336;
do over t;
 t=z;
end;
run;

[dapenDaniel]

Thanks!