update a table based on the values of another table

[Sheeba]

HI,

Is it possible to update a table A based on the values of table B using SAS DI studio?

Table A and Table B has a common variable Var1

I need to update the column of A if  matching values of Var1 exists in B.

Thanks in advance,

Regards,

Sheeba Swaminathan

[Scott_Mitchell]

Create a user written transformation and use a datastep update statement.  You could also use a hash table, but that would depend on the size of the table and the amount of memory you have available.

DATA A;

DO ID = 1 TO 10;

AMOUNT = INT(RANUNI(0)*500);

OUTPUT;

END;

RUN;

PROC SORT DATA=&SYSLAST.;

BY ID;

RUN;

DATA B;

DO ID = 1 TO 10 BY 2;

AMOUNT = INT(RANUNI(0)*500);

OUTPUT;

END;

RUN;

PROC SORT DATA=&SYSLAST.;

BY ID;

RUN;

DATA WANT;

  UPDATE A

           B;

  BY ID;

RUN;

[Sheeba]

Thanks Scott. I will try out this method.

[LinusH]

From you description, it sounds like standard processing, which could be performed by some standard transformations, probably no need for User Written. If B is seen as a transactions/changed data, use the Table Loader (Update Load method).

SQL Join could also work. Which one to use depends on the nature of your application.

[Sheeba]

Thanks Linush.

I will try out that as I want to reduce the user written transformations.

[Michael_SAS]

Take a look at the Update transform DI Studio 4.4. it supports Updates with Correlated Sub Queries

update table A

set column_A = 

( SELECT column_B

   FROM B                         

   WHERE  B.Var1 = A.Var1)

[Sheeba]

Thanks Michael.

I will try out the update transform with exist keyword.