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.
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;
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;
Thank you @novinosrin
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.
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
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;
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;
Thanks!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.