SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

update a table based on the values of another table

Reply
Regular Contributor
Posts: 167

update a table based on the values of another table

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

Super Contributor
Posts: 297

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

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;

Regular Contributor
Posts: 167

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

Posted in reply to Scott_Mitchell

Thanks Scott. I will try out this method.

Super User
Posts: 5,431

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

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.

Data never sleeps
Regular Contributor
Posts: 167

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

Thanks Linush.

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

SAS Employee
Posts: 13

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

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)

Regular Contributor
Posts: 167

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

Posted in reply to Michael_SAS

Thanks Michael.

I will try out the update transform with exist keyword.

Ask a Question
Discussion stats
  • 6 replies
  • 729 views
  • 0 likes
  • 4 in conversation