Date:         Tue, 19 Sep 2006 04:05:21 -0400
Reply-To:     Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Subject:      Re: WHERE clause with LIKE and wild cards, redux
Comments: To: Talbot Michael Katz <topkatz@MSN.COM>

Hi Talbot,

Is this what you would like? Just quote the % character while allowing the &
to resolve:

data testwql1 ;
  input a b $ ;
  cards ;
  1 q
  1 e
  2 y
  2 q
  ;
run ;

* test where with wilds ;
%let tv = q ;
proc sql ;
  create table testwql2 as
  select *
  from testwql1
  where b like "%NRSTR(%%)&tv%NRSTR(%%)"
  order by a, b ;
quit ;

PROC PRINT DATA=Testwql2; RUN;

In the SQL procedure I changed: create table testwql1
into: create table testwql2
because I initially got the following error:
WARNING: This CREATE TABLE statement recursively references the target
table. A consequence of
         this is a possible data integrity problem.

Regards - Jim.
--
Jim Groeneveld, Netherlands
Statistician, SAS consultant
home.hccnet.nl/jim.groeneveld

My computer remains home while I will attend PhUSE 2006 in Dublin.


On Tue, 19 Sep 2006 03:35:04 -0400, Talbot Michael Katz <topkatz@MSN.COM> wrote:

>Hi gang.
>
>Returning to a puzzle I got help with recently.  Consider the following
>very simple example:
>
>data testwql1 ;
> input a b $ ;
>cards ;
>1 q
>1 e
>2 y
>2 q
>;
>run ;
>
>* test where with wilds ;
>proc sql ;
> create table testwql1 as
> select   *
> from   testwql1
> where   b like "%q%"
> order by  a, b
> ;
>quit ;
>
>
>This works correctly, giving the following output:
>                                           Obs    a    b
>
>                                            1     1    q
>                                            2     2    q
>
>but it also issues the following warning:
>WARNING: Apparent invocation of macro Q not resolved.
>
>
>If we use single quotes:
>
>* test where with wilds ;
>proc sql ;
> create table testwql2 as
> select   *
> from   testwql1
> where   b like '%q%'
> order by  a, b
> ;
>quit ;
>
>
>it works perfectly, with no warnings.  I'm already confused.
>
>
>But suppose we wanted to specify our search string with a macro variable:
>
>
>
>%let tv = q ;
>
>* test where with wilds ;
>proc sql ;
> create table testwql2 as
> select   *
> from   testwql1
> where   b like "%&tv.%"
> order by  a, b
> ;
>quit ;
>
>
>This does not work correctly (empty output), and issues the same warning:
>WARNING: Apparent invocation of macro Q not resolved.
>
>
>Of course, it still doesn't work if we change the double quotes to single
>quotes, since macro variables won't resolve between single quotes, but it
>doesn't issue the warning.
>
>
>Now we try the trick you showed us recently (with the single quotes):
>
>* test where with wilds ;
>proc sql ;
> create table testwqlm3 as
> select   *
> from   testwql1
> where   b like %unquote(%str(%'%%)%str
>(&tv.)%str(%%%'))
> order by  a, b
> ;
>quit ;
>
>This works perfectly with no warnings, but if we use double quotes:
>
>* test where with wilds ;
>proc sql ;
> create table testwqlm4 as
> select   *
> from   testwql1
> where   b like %unquote(%str(%"%%)%str
>(&tv.)%str(%%%"))
> order by  a, b
> ;
>quit ;
>
>
>it works, but it still issues the warning.
>
>***  QUESTION  ***  Is there a way to use double quotes and not get the
>warning?
>
>
>THANKS!
>
>
>--  TMK  --
>"The Macro Klutz"