HI all,
I have two tables,
data test;
infile datalines;
input obs product $ quantity;
datalines;
1 A 2
2 B 3
3 C 5
4 D 6
;
run;
data test2;
infile datalines;
input obs product $ quantity;
datalines;
1 A 5
2 D 8
;
run;
I want to create a datasets as follows:
1 A 5
2 B 3
3 C 5
4 D 8
A and D gets updated value from test2? Is there way to achieve this using PROC SQL??
Thanks for the help in advance..
proc sql;
update test as a
set quantity=(select quantity from test2 b where a.product=b.product) where a.product in (select product from test2);
quit;
or
data test;
update test test2;
by product;
run;
Why SQL? This is easier in a data step.
Try this.
proc sql;
select a.obs,a.product,COALESCE(b.quantity,a.quantity)
from test a left join test2 b
on a.product=b.product;
quit;
proc sql;
update test as a
set quantity=(select quantity from test2 b where a.product=b.product) where a.product in (select product from test2);
quit;
or
data test;
update test test2;
by product;
run;
thank you all for your suggestion
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.