2

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*/
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
lord12
  • 2,837
  • 9
  • 35
  • 47

2 Answers2

6

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).

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • +1! thanks for this reproducible solution! I would just add the market variable in the final output `select a.*, b.Code,b.market` to make the solution clearer clearer. – agstudy Feb 23 '14 at 08:16
1

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.

ps2goat
  • 8,067
  • 1
  • 35
  • 68