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
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;
Thanks Scott. I will try out this method.
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.
Thanks Linush.
I will try out that as I want to reduce the user written transformations.
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)
Thanks Michael.
I will try out the update transform with exist keyword.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.