topic Re: one-to-many match in New SAS User

one-to-many match

dapenDaniel — Thu, 27 Feb 2020 20:11:26 GMT

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

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.

Re: one-to-many match

novinosrin — Thu, 27 Feb 2020 20:16:14 GMT

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;

Re: one-to-many match

dapenDaniel — Thu, 27 Feb 2020 20:18:19 GMT

Thank you @novinosrin

Re: one-to-many match

dapenDaniel — Thu, 27 Feb 2020 20:23:06 GMT

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.

Re: one-to-many match

novinosrin — Thu, 27 Feb 2020 20:32:38 GMT

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

Re: one-to-many match

dapenDaniel — Thu, 27 Feb 2020 20:43:07 GMT

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;

Re: one-to-many match

novinosrin — Thu, 27 Feb 2020 20:48:07 GMT

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;

Re: one-to-many match

dapenDaniel — Mon, 02 Mar 2020 06:18:11 GMT

Thanks!