Hi,
I am having the hardest time trying to figure out the SAS code to do the following: Assume I have two datasets (A) and (B)
(A)
Product | Price |
A | $3 |
B | |
C | $5 |
D | $8 |
E |
(B)
Product | Price | Quantity |
A | $3 | 1 |
B | $10 | 2 |
E | $12 | 4 |
I would like to left join (A) with (B) to get quantity (when it is available) and if price is missing in (A) then I would like to take the price from (B).
So, the dataset I would like to end up with looks like this:
(C)
Product | Price | Quantity |
A | $3 | 1 |
B | $10 | 2 |
C | $5 | |
D | $8 | |
E | $12 | 4 |
Thanks in advance!
Looks like something you can do easily with normal SAS statements.
In this case the UPDATE statement. You want to update the dataset with product/price lists with the information from the dataset that also has quantity information.
data prices;
input Product $ Price :comma.;
cards;
A $3
B .
C $5
D $8
E .
;
data quantities ;
input Product $ Price :comma. Quantity;
cards;
A $3 1
B $10 2
E $12 4
;
data want;
update prices quantities;
by product;
run;
Result
You did not show any situations where the product was in both dataset, but in that case any non-missing value of price in the transaction dataset (the one with the quantity variable) would replace the price from the original dataset (the one only the product and price).
proc sql;
create table c as
select
a.product,
coalesce(a.price,b.price) as price,
b.quantity
from a left join b
on a.product = b.product
;
quit;
Looks like something you can do easily with normal SAS statements.
In this case the UPDATE statement. You want to update the dataset with product/price lists with the information from the dataset that also has quantity information.
data prices;
input Product $ Price :comma.;
cards;
A $3
B .
C $5
D $8
E .
;
data quantities ;
input Product $ Price :comma. Quantity;
cards;
A $3 1
B $10 2
E $12 4
;
data want;
update prices quantities;
by product;
run;
Result
You did not show any situations where the product was in both dataset, but in that case any non-missing value of price in the transaction dataset (the one with the quantity variable) would replace the price from the original dataset (the one only the product and price).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.