topic Left Join but Fill Missing Values from Other Tables in SAS Programming

Left Join but Fill Missing Values from Other Tables

jc19 — Mon, 07 Oct 2024 22:28:08 GMT

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!

Re: Left Join but Fill Missing Values from Other Tables

Kurt_Bremser — Mon, 07 Oct 2024 22:56:41 GMT

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;

Re: Left Join but Fill Missing Values from Other Tables

Tom — Tue, 08 Oct 2024 16:53:04 GMT

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).