Date:         Tue, 1 Jul 2003 17:19:41 -0700
Reply-To:     WCheng@ISISPH.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Wei Cheng <WCheng@ISISPH.COM>
Subject:      Re: Macro quoting (was  macro resolution in Proc SQL)
Comments: To: "Huang, Ya" <yhuang@AMYLIN.COM>
Content-Type: text/plain; charset="us-ascii"

Ya,

I think you can just add the %unquote() function to it.

%let a=SUBJID     VISITDATE;
%let b=%str(%')%sysfunc(tranwrd(%sysfunc(compbl(&a)),%str(
),%str(',')))%str(%');
%put &b;

proc sql noprint;
create table cont as
select * from
dictionary.columns
where libname='WORK'
and memname='TEST'
and upcase(name) in (%unquote(&b));
;

HIH,

Wei






"Huang, Ya" <yhuang@AMYLIN.COM>
Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
07/01/03 04:47 PM
Please respond to "Huang, Ya"


        To:     SAS-L@LISTSERV.UGA.EDU
        cc:
        Subject:        Macro quoting (was  macro resolution in Proc SQL)


Susie,

I realized this before seeing your response. I was
playing around the following, I almost got what
we want, but somehow, it failed to pick up the correct
record. I wonder if I need some special macro quoting
function. I really hate this macro quoting thing :-(
Hopefully someone here can fix my problem.


%let a=SUBJID     VISITDATE;
%let b=%str(%')%sysfunc(tranwrd(%sysfunc(compbl(&a)),%str(
),%str(',')))%str(%');
%put &b;

proc sql noprint;
create table cont as
select * from
dictionary.columns
where libname='WORK'
and memname='TEST'
and upcase(name) in (&b);
;

SYMBOLGEN:  Macro variable A resolves to SUBJID     VISITDATE
1121  %put &b;
SYMBOLGEN:  Macro variable B resolves to 'SUBJID','VISITDATE'
SYMBOLGEN:  Some characters in the above value which were subject to macro
quoting have been
            unquoted for printing.
'SUBJID','VISITDATE'
1122
1123  proc sql noprint;
1124  create table cont as
1125  select * from
1126  dictionary.columns
1127  where libname='WORK'
1128  and memname='TEST'
1129  and upcase(name) in (&b);
SYMBOLGEN:  Macro variable B resolves to 'SUBJID','VISITDATE'
SYMBOLGEN:  Some characters in the above value which were subject to macro
quoting have been
            unquoted for printing.

      -
      22
       ------
       202
ERROR 22-322: Syntax error, expecting one of the following: a quoted
string,
              a numeric constant, a datetime constant, a missing value, (,
-, SELECT.


Kind regards,

Ya

-----Original Message-----
From: uset sas [mailto:sasuser9000@YAHOO.CO.UK]
Sent: Tuesday, July 01, 2003 4:12 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: macro resolution in Proc SQL


Ya,
Thanks for your quickest response. My problem is that I get the values
from a macro variable as
%let a=subjid visitdate;
sometime I may get up to 10 varibles as %let a=var1 var2 var3 var4 var4
var5 var6 var7 var8 var9 var10;
Based on this I want to construct the where condition. The reason for this
is we use the macro variable in other datasteps. So the only way for me is
to construct the where condition dynamically based on the values of macro
variable a.
if I have a macro variable %let a=var1 var2 var3 var4 var4 var5 var6 var7
var8 var9 var10;
I need to make it as a='var1', 'var2', 'var3', 'var4', 'var4', 'var5',
'var6', 'var7','var8', 'var9', 'var10';
So I can use this in my where condition.
Kind regards,
Susi leu

"Huang, Ya" <yhuang@amylin.com> wrote:
Hi Susie,

Try add quotation mark around those names in the %let
statement, also don't forget to use upcase function:

%let a='SUBJID','VISITDATE';
proc sql noprint;
create table cont as
select * from
dictionary.columns
where libname='WORK'
and memname='TEST'
and upcase(name) in (&a)
;

Kind regards,

Ya

-----Original Message-----
From: uset sas [mailto:sasuser9000@YAHOO.CO.UK]
Sent: Tuesday, July 01, 2003 3:25 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: macro resolution in Proc SQL


Hello,

I have a macro variable that containing the column names. How can I make
this one work in the where condition. I want to give the option to the
user to select as many columns he wants.
data test;
input subjid visitdate;
format visitdate date9.;
cards;
100 12345
101 13456
102 15890
;
run;
%let a=SUBJID VISITDATE;
proc sql noprint;
create table cont as
select * from
dictionary.columns
where libname='WORK'
and memname='TEST'
/* and name in ("&wherecond.") need help here*/
name in('SUBJID','VISITDATE')
;
quit;
Kind Regards,
Susi Leu


---------------------------------
Want to chat instantly with your online friends? Get the FREE
Yahoo!Messenger


---------------------------------
Want to chat instantly with your online friends? Get the FREE
Yahoo!Messenger