Date:         Tue, 4 Apr 2006 11:20:37 -0400
Reply-To:     Venky Chakravarthy <swovcc@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Venky Chakravarthy <swovcc@HOTMAIL.COM>
Subject:      Re: quoted string in PROC SQL - using macro quoting functions
Comments: To: Rushi Patel <rushi.b.patel@GMAIL.COM>

Rushi,

Your macro does produce the intended results. Simply UNQUOTE and it works.

PROC SQL ;
select name
from <data>
where name in (%unquote(%mac1));
quit;

Venky Chakravarthy

On Tue, 4 Apr 2006 10:38:38 -0400, Rushi Patel <rushi.b.patel@GMAIL.COM>
wrote:

>Venky,
>
>Thanks for your reply.
>
>I thought about quote function but feel that won`t work here becuase
>the objective is the opposite..Instead of quoting variables present in
>ONE dataset, I want to check which variables defined in %let variables
>= are present in various datasets..The string in &code is to be passed
>to a bunch of datasets...
>
>Rushi
>
>On 4/4/06, Venky Chakravarthy <swovcc@hotmail.com> wrote:
>> Rushi,
>>
>> The fix may be further upstream and perhaps simpler. It looks like you
have
>> a list of variables and you are trying to single quote with a macro.
>> Instead of that you could use the QUOTE function while creating your list
>> of variables. For example, the following produces a list of the first
five
>> names from SASHELP.CLASS.
>>
>> proc sql noprint inobs = 5 ;
>>   select quote(trim(left(name))) into : namelist separated by " "
>>     from sashelp.class ;
>> quit ;
>>
>> %put <<<&namelist>>> ;
>>
>> This gives
>> 168  %put <<<&namelist>>> ;
>> <<<"Alfred" "Alice" "Barbara" "Carol" "Henry">>>
>>
>> Is this what you want?
>>
>> Venky Chakravarthy
>>
>> On Tue, 4 Apr 2006 10:12:36 -0400, Rushi Patel <rushi.b.patel@GMAIL.COM>
>> wrote:
>>
>> >I intend to dynamically use a string in the PROC SQL where clause..I
>> >have dipped my hands into Macro quoting to achieve this and have a few
>> >questions..
>> >
>> >given a list of variables defined in %variables= var1 var2 var3;, I
>> >want to execute..
>> >
>> >PROC SQL;
>> >select name
>> >from <data>
>> >where name in ('var1','var2','var3');
>> >quit;
>> >run;
>> >
>> >To dynamically code the string 'var1','var2',.... I have tried to code
>> >the following macro.
>> >
>> >%let count = 3; /* a counter set to 3 because we know there are three
>> >variables at present */
>> >
>> >%macro mac1;
>> >
>> >%let i = 1;
>> >        %let step = %scan(&variables,&i);
>> >        %let code =;
>> >
>> >        %do %while (%length(&step)>0);
>> >
>> >                %if &i < &count %then %do;
>> >                        %let code = &code%nrquote(%bquote('))&step%
nrquote
>> (%bquote('))%str(,);
>> >                %end;
>> >
>> >                %if &i = &count %then %do;
>> >                        %let code = &code%nrquote(%bquote('))&step%
nrquote
>> (%bquote('));
>> >                %end;
>> >
>> >                %let i = %eval(&i + 1);
>> >                %let step = %scan(&variables,&i);
>> >
>> >        %end;
>> >                &code
>> >
>> >%mend mac1;
>> >
>> >Upon invoking this macro as
>> >
>> >PROC SQL
>> >select name
>> >from <data>
>> >where name in (%mac1);
>> >quit;
>> >
>> >I get errors..
>> >
>> >However, when I look at the log  -- using %put &code, instead of &code
>> >-- I see the exact statements that I want, i.e. 'var1','var2',var3'
>> >
>> >Thanks,
>> >
>> >Rushi
>>