Date: Tue, 23 Nov 2010 12:22:01 -0500
Reply-To: Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Subject: Re: SQL LIKE condition with macro variable
maybe the macro preprocessor "eats" one. If there is one that is a macro-
trigger and is removed when code is generated. So in the first pass
through
%nrstr(%)
the argument is blank. If you have %% in first pass the argument for nrstr
is (%)
Same thing if you have code which contains
%INCLUDE's
If you place that in a macro, you should use
%%INCLUDE
Gerhard
On Tue, 23 Nov 2010 12:09:18 -0500, Jerry <i89rt5@GMAIL.COM> wrote:
>"Data _null_", thank you so much for the solution!!! I always learn so
much
>from you. One follow-up question:
>
>You said "I used %NRSTR around the first % to get the desired result.",
but
>when you used %NRSTR, you actually have two % in it: %nrstr(%%)
>
>I would like to understand why you actually put two % in it, because when
I
>tried %nrstr(%), it didn't work.
>
>Thank you again!
>
>Jerry
>
>On Tue, 23 Nov 2010 10:36:17 -0600, Data _null_; <iebupdte@GMAIL.COM>
wrote:
>
>>It seems like %& is the problem. Use the PROC SQL statement option
>>FEEDBACK to debug. I used %NRSTR around the first % to get the
>>desired result.
>>
>>966 proc sql noprint feedback;
>>967 select memname
>>968 into :dataset separated by ' '
>>969 from dictionary.members
>>970 where libname = "WORK" AND MEMTYPE='DATA' AND MEMNAME like
>>"%nrstr(%%)&id.^_&year.%" escape '^'
>>971 ;
>>NOTE: Statement transforms to:
>>
>> select memname
>> from DICTIONARY.MEMBERS
>> where (libname = 'WORK') and (memtype = 'DATA') and memname
>>like '%1234^_2010%' escape '^';
>>
>>972 quit;
>>973 run;
>>974
>>975 %put NOTE: dataset=&dataset;
>>NOTE: dataset=PRE1234_2010 PRE1234_2010POST PRE1234_2010PRE
>>
>>
>>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.
>>>
| 
