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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.