without knowing more about the table layouts...  You could also create a view.  Depending on the size of the lookup table and if it was a sas dataset or an external dbms table, a hash lookup might be faster but comes with more complexity. proc sql; create table want as select b.id_1, t1.lookup as id_1_lookup, b.id_2, t2.lookup as id_2_lookup, b.id_3, t3.lookup as id_3_lookup b.id_4, t4.lookup as id_4_lookup, b.id_5, t5.lookup as id_5_lookup from      base b      left outer join lookup_table t1           on b.id_1=t1.id      left outer join lookup_table t2           on b.id_2=t2.id      left outer join lookup_table t3           on b.id_3=t3.id      left outer join lookup_table t4           on b.id_4=t4.id      left outer join lookup_table t5           on b.id_5=t5.id ; quit; ... View more

A more normal data structure might look something like this (presuming SOC is the unique key): SOC_Dim SOC SOC_Description Feature_Code Product_Type SOC_Fact SOC Business_Date Rate You would need to insert new products records into SOC_DIM each month and all records into the fact table: proc sql; create table work.soc_new as select      t1.SOC      t1.SOC_Description      t1.Feature_Code      t1.Product_Type from      NewData t1      left outer join work.soc_dim t2      on t1.soc=t2.soc where      t2.soc is null; insert into soc_dim (      SOC      SOC_Description      Feature_Code      Product_Type) select      SOC      SOC_Description      Feature_Code      Product_Type     from work.soc_new; insert into SOC_FACT (      soc,      business_Date,      rate) select      soc,      business_date, /*not sure where the date actually comes from*/      rate from      work.new_data; quit; Then, it's a fairly straightforward question to use in queries/views: proc sql; select      t1.*,      t2.rate as Rate_20141231 from      soc_dim t1      left outer join (select * from soc_dim where business_Date='31dec2014'd) t2           on t1.soc=t2.soc ; quit; ... View more

Wouldn't that eliminate the quotes even when they aren't part of the phrase that's being suppressed? ... View more

I'm not sure.  Might have to test that. As far as table size, it always depends on where the original data lives.  If it is a SAS dataset, then using a hash function in conjunction with a data step modify  might be fastest.  If the source is some other data management system (i.e. Oracle), then you'll probably have to create a separate dataset anyway. ... View more

I'm sure there's a way to do it with hash tables...but here's a relatively straight forward approach: data companies; format Company_Name $50.; input Company_Name $50.; datalines; Awolsk Kanovereniging "De Baldelaars" Awolsk Kanovereniging "De Maldelaars" Awolsk Kanoverrriging "De Surdelaars" Awolsk Kanovereniging "De Udadelaars" Awolsk Kanovereniging "De Peddelaars" ; run; data patterns; format Phrase $50.; input Phrase $50.; datalines; "De Peddelaars" "De Surdelaars" ; run; proc sql; create table want as select     tranwrd(c.company_name,trim(p.phrase),'') as test from     companies c     left outer join  patterns p         on c.company_name like cats("%",p.phrase,"%"); quit; produces Awolsk Kanovereniging Awolsk Kanoverrriging Awolsk Kanovereniging "De Baldelaars" Awolsk Kanovereniging "De Maldelaars" Awolsk Kanovereniging "De Udadelaars" ... View more

nice to know.  I'm figured that STAT wouldn't have made an error like that..but had to ask. ... View more

Isn't the value for name going to be a period? Shouldn't it be if name='.' then name='Missing'; ... View more