update a table with the another subselect.

[ger15xxhcker]

Hi,

I have a table with ~200k rows and i have to update it from another table which has the same columns, but some columns has different values in it .How can i update the first table with the second? I have 2 ID -s in it.

Thanks for all.

[mkeintz]

Please show a sample of the two datasets before and the single dataset after.

[ger15xxhcker]

base table:

id1    id2     a     b    c

   1     10   x1   x2   x3

   2     20   y1   y2   y3 

   3     30   z1   z2   z3

   4     40   r1    r2   r3

i got this table from a source

id1    id2     a     b    c

   1     10   x1   x2   x3

   2     20   y1   y2   y3 

   3     30   z4   z5   z3

and i have to update the base table with the second one like this:

final:

id1    id2     a     b    c

   1     10   x1   x2   x3

   2     20   y1   y2   y3 

   3     30   z4   z5   z3

   4     40   r1    r2    r3

[PGStats · Accepted solution]

Use the update statement. That's exactly what it does.

data a;
input id1    id2     a $     b $    c $;
datalines;
   1     10   x1   x2   x3
   2     20   y1   y2   y3 
   3     30   z1   z2   z3
   4     40   r1   r2   r3
;
 
data b;
input id1    id2     a $     b $    c $;
datalines;
   1     10   x1   x2   x3
   2     20   y1   y2   y3 
   3     30   z4   z5   z3
;

data c;
update a b;
by id1 id2;
run;

proc print; run;