sql - Update a table using another table in SAS -

i have table t1 , t2, how can create table t3? want update var2 in t1 t1.key=t2.key, using data t2 while keeping else in t1 same.

table t1

key var1 var2  1       aa 2    b    bb 3    c    cc 4    d    dd 5    e    ee 

table t2

key var1 var2 3    c    xx 4    d    yy 6    f    ff 

table t3

key var1 var2 1       aa 2    b    bb 3    c    xx 4    d    yy 5    e    ee 

the following sas code give me errors:

proc sql; update t1  set  var2 = t2.var2 t1 inner join t2 on  t1.key=t2.key; quit; 

thanks!

you can use update statement in data step. update statements similar merge statements, except not replace populated values missing values unless specify. in addition, non-unique keys generate warning, helpful debugging.

the general syntax updating tables , creating new 1 simultaneously:

syntax

data newtable;     update mastertable            transactiontable;     key(s); run; 

in order update operation data, need make sure 2 datasets either sorted or indexed key variable. if it's not sorted or indexed, sas complain , update fail. in general, add index option @ final steps need create master or transactional datasets. tends save time because additional sorting step prevented.

step 1: make sure tables 1 & 2 either indexed or sorted key

data t1(index=(key) );     <etl stuff>; run;  data t2(index=(key) );     <etl stuff>; run; 

step 2: create new table, t3, updating t1 matching values t2

data t3(index=(key) );    update t1(in=t1_key)  /* same thing sql t1.key */           t2(in=t2_key); /* same thing sql t2.key */    key;     /* sas's way of specifying left-join */    if(t1_key); run; 

though not need index= option on t3 here, practice keep key variable indexed or have integrity constraint applied it.