PROC SQL like operator

Question

I was just wondering if there is a way to implement a like with a % sign for a variable. I basically want region to contain market and any other characters for there to be a match. An example is if REGION = ALBANY-SXSX-TVTV and market=ALBANY,I want there to be a match between region and market.

select a.*, b.Code,b.HHLDS
from Master1 as a left join map as b
on a.region like "b.market%"; /*not sure about this*/ 

Solution 1

Based on the comment from agstudy, here is a solution very similar to the one ps2goat gave, with some simple sample data:

data Master1;
   length region $30;
   input region;
   datalines;
ALBANY-SXSX-TVTV
ALBANY2-SXSX-TVTV
SEATTLE-SXSX-TVTV
NEWYORK-SXSX-TVTV
run;

data map;
   length market $10 code $1;
   input market code;
   datalines;
ALBANY A
SEATTLE B
run;

proc sql noprint;
   create table a as
   select a.*, b.Code
   from Master1 a 
   left join map b
   on a.region like '%' || trim(b.market) || '%';
quit;

Please note that this used the concatenation operator (||) instead of the CAT function. However, CAT doesn't work; to use ps2goat's solution, you must use the CATS function to remove extraneous blanks. So it cab also be written as

   on a.region like CATS('%',b.market),'%');

Also note the use of single quotes; never use double quotes when making a character constant. Text inside double-quotes is scanned by the Macro Processor for symbol substitution (and the percent symbol is a trigger).

Solution 2

just use concatenation:

select a.*, b.Code,b.HHLDS
from Master1 as a left join map as b
on a.region like CAT("%",b.market,"%");

updated to use SAS concatenating function, and double quotes for string values.