I am fairly new to sas and I am wrapping my head around this problem. I have two tables A and B. The first one collects information about customers in terms of their purchases and the specific products they purchased

A

The second one is some sort of a dictionary, i.e. contains old and new codes, the old code being the key and the new one the updated version.

B

My goal would be to update in table A, all the references to old codes with their updated (new) version. In the end A should look like

The approach that I wuld take in this example is the following (pseudo-code)

if A.product_code in B.old then
   A.product_code = B.new
else
   nothing

But I am struggling a bit with sas synthax to implement that.

I really hope that my issue is clear enough and do not hesitate to ask further clarification if necessary.

Thanks to anyone who is willing to participate

CodePudding user response：

How about

data a;
input Customer_id product_code;
datalines;
1111111 12345
1111111 34523
;

data b;
input old new;
datalines;
34523 22256
89765 76576
;

proc sql;
   update a
   set product_code = 
      (select new from b
      where a.product_code = b.old)
   where exists (
      select 1
      from b
      where a.product_code = b.old)
   ;
quit;

CodePudding user response：

A very SAS way is to use MODIFY statement and hash lookup.

data master;
  modify master;
  if _n_ = 1 then do;
    declare hash mappings(dataset:'code_changes(rename=new_code=code)');
    mappings.defineKey('old_code');
    mappings.defineData('code');
    mappings.defineDone();
    call missing(old_code);
  end;

  if mappings.find(key:code)=0 then replace;
run;

Another MODIFY way is to read changes with a SET statement.

This example requires an index on the master table.

proc sql;
  create index code on master;

data master;
  set mappings;

  reset = 1;
  do until (_iorc_);
    code = old_code;
    modify master key=code keyreset=reset;

    if _iorc_ = 0 then do;
      code = new_code;
      replace;
    end;
    reset = 0;
  end;

  _error_ = 0;
run;