topic Re: update a table based on the values of another table in SAS Data Management

update a table based on the values of another table

Sheeba — Wed, 11 Sep 2013 01:20:09 GMT

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

Re: update a table based on the values of another table

Scott_Mitchell — Wed, 11 Sep 2013 02:14:22 GMT

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

BY ID;

RUN;

Re: update a table based on the values of another table

LinusH — Wed, 11 Sep 2013 11:53:16 GMT

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.

Re: update a table based on the values of another table

Michael_SAS — Wed, 11 Sep 2013 15:31:38 GMT

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)

Re: update a table based on the values of another table

Sheeba — Wed, 11 Sep 2013 20:48:03 GMT

Thanks Scott. I will try out this method.

Re: update a table based on the values of another table

Sheeba — Wed, 11 Sep 2013 20:49:33 GMT

Thanks Linush.

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

Re: update a table based on the values of another table

Sheeba — Wed, 11 Sep 2013 20:55:45 GMT

Thanks Michael.

I will try out the update transform with exist keyword.