Date:         Tue, 23 Nov 2010 10:10:52 -0600
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: SQL LIKE condition with macro variable
Comments: To: Jerry <i89rt5@gmail.com>
In-Reply-To:  <201011231559.oANBlLxX007378@willow.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

%let letter=z;

proc sql noprint ;
 select memname
 into :dataset separated by ' '
 from dictionary.members
 where libname = "SASHELP" AND MEMTYPE='DATA' AND MEMNAME like "z%"
 ;
quit;


also gives no rows...

and
%let letter=Z;

proc sql noprint ;
 select memname
 into :dataset separated by ' '
 from dictionary.members
 where libname = "SASHELP" AND MEMTYPE='DATA' AND MEMNAME like "&letter.%"
 ;
quit;

gives rows.

I'd guess you're missing the 'case-sensitive' bit.

-Joe

On Tue, Nov 23, 2010 at 9:59 AM, Jerry <i89rt5@gmail.com> wrote:

> Hi,
>
> Within PROC SQL, the "LIKE" condition is used to test for a matching
> pattern, and is case-sensitive.
>
> Per SAS documentation at
>
> http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473693.htm
>
> underscore (_) is used to match any single character.
>
> percent sign (%) is used to match any sequence of zero or more characters.
>
> any other character is used to matche that character.
>
> Because the % and _ characters have special meaning in the context of the
> LIKE condition, the ESCAPE clause is used to search for these character
> literals in the input character string.
>
> Here is my headache when I tried to use LIKE condition together with macro
> variable, see my example below
>
> LIKE "%1234^_2010%" matches this value: pre1234_2010post
>
> Now let me set up 2 macro variables to replace 1234 and 2010:
> %let id=1234;
> %let year=2010;
>
> However, LIKE "%&id.^_&year.%" does not work, how to fix it?
>
> Another example which may better explain my question is below, as it does
> not involve the underscore (_)
>
> /*******THIS WORKS************/
> proc sql noprint ;
>  select memname
>  into :dataset separated by ' '
>  from dictionary.members
>  where libname = "SASHELP" AND MEMTYPE='DATA' AND MEMNAME like "Z%"
>  ;
> quit;
>
> %put "DATASET is &DATASET";
> /**********
> In the log, you will see
> "DATASET is ZHC ZIPCODE ZIPMIL ZTC"
> **********/
>
> /************
> THIS does not WORKS, when I replace "z" with a macro variable &letter
> *************/
> %let letter=z;
>
> proc sql noprint ;
>  select memname
>  into :dataset separated by ' '
>  from dictionary.members
>  where libname = "SASHELP" AND MEMTYPE='DATA' AND MEMNAME like "&letter%"
>  ;
> quit;
>
> %put "DATASET is &DATASET";
> /***************
> In the log, you will see this
> NOTE: No rows were selected.
> *****************/
>
> Let me know if my question is not clear.
>
> Thanks.
>