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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.