CODEWITHSUNDEEP
sas
Richard
Richard

Reputation: 41

How to match data in SAS

I have a dataset which contain three variables var1, var2, and Price. Price is the price of var2. var1 is a subsample of of Var2. Now, I want to find the price of each product in var1 by matching the name of Var1 with Var2. The data looks like this. Can anyone help me solve this out please. Many thanks

Var1    Var2    Price
apple           ?
        apple   2
banana          ?
        banana  2.1
apple           ?
orange          ?
        orange  4
banana          ?
        yoghurt 2

Upvotes: 0

Views: 96

Answers (2)

Shenglin Chen
Shenglin Chen

Reputation: 4554

Try to use hash table.

 data want;
   if 0 then set have(keep=var2 price where=(not missing(var2)));
   if _n_=1 then do;
      declare hash h (dataset:'have1(keep=var2 price where=(not missing(var2)))');
      h.definekey('var2');
      h.definedata('price');
      h.definedone();
      call missing(var2,price);
    end;
    set have;
    rc=h.find(key:var1);
    drop rc;
    run;

Upvotes: 0

Bendy
Bendy

Reputation: 3576

You could do this through SQL by merging your prices onto your dataset by var1/var2:

proc sql ;
create table output as
    select a.var1, a.var2, b.price
    from input a
    left join (select distinct var2, price
      from input 
      where not missing(var2)) as b
    on (a.var1=b.var2
    or a.var2=b.var2)
;quit ;

Upvotes: 1

Related Questions