strings with special characters comparison

[glz]

Hello all,

I have an example data:

The column in data A includes special characters: AA*B, A**B, AB**, and here * can be anything, both letter and number. 

The column in data B without special character: AADB, ABBF, AB3D.

so in this example, since AADB satisfies AA*B, AB3D satisfies AB**, they will be reported.

I do not know how to deal with them in SAS, could anyone help me to figure this out?

Thanks a lot. 

[Tom]

Sounds a lot like what the LIKE operator can handle.

You can use LIKE in SQL queries.

If the text literally has * to represent a single character you will want to convert those to _ which is what the LIKE operator uses to match a single character.

proc sql;
  create table want as 
  select a.*,b.xxx
  from table1 A 
    left join table2 B
   on a.column like translate(b.column,'_','*')
  ;
quit;

If the values in the pattern can include actual _ characters you will need to first prefix those with an escape character.

   on a.column like translate(tranwrd(b.column,'_','^_),'_','*') escape '^'

If you cannot find an escape character to use that cannot appear in the pattern string then you will need to escape those also.

   on a.column like translate(tranwrd(tranwrd(b.column,'^','^^'),'_','^_),'_','*') escape '^'