Date: Mon, 5 Feb 2001 12:03:10 -0800
Reply-To: "Lund, Pete" <Peter.Lund@WSIPP.WA.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Lund, Pete" <Peter.Lund@WSIPP.WA.GOV>
Subject: Re: What do I unquote?
Content-Type: text/plain; charset="iso-8859-1"
Hi Jonathan-
Your problem is not with quoting but the fact that the variables
created by you macroized SELECTs are local. You can see that the macro
variables are created correctly, in a local environment, by adding this
line to your original macro:
> %macro GetNofGens;
> %local GDataset i;
> %let i = 1;
> %let GDataset = %scan(&GDGS, &i);
> %do %while (&GDataset ^=);
> %unquote((&GDataset
> select put(count(*), 1.) into :&GDataset.count
> from dictionary.tables
> where
> memname contains '#' and
> upcase(memname) contains upcase(%str(%')&GDataset%str(%'))
> )
> ;
%put COUNT: &&&GDataset.count; /* <---- Add this line */
> %let i = %eval(&i + 1);
> %let GDataset = %scan(&GDGS, &i);
> %end;
> %mend GetNofGens;
I'd suggest making a slight change and embedding the entire SQL
procedure in the macro and "globalize" the variables there. The only
real cost is going to be that of initializing PROC SQL multiple times -
should be minimal at worst.
Try this version of your original code:
%macro GetNofGens;
%local GDataset i;
%let i = 1;
%let GDataset = %scan(&GDGS, &i);
%do %while (&GDataset ^=);
%global &GDataset.count; /* Add the GLOBAL here, before the PROC
SQL */
proc sql; /* Put the PROC SQL in the macro
*/
%unquote((&GDataset
select put(count(*), 1.) into :&GDataset.count
from dictionary.tables
where
memname contains '#' and
upcase(memname) contains upcase(%str(%')&GDataset%str(%'))
)
;
quit; /* Add a QUIT; */
%let i = %eval(&i + 1);
%let GDataset = %scan(&GDGS, &i);
%end;
%mend GetNofGens;
Your call would then just be:
%GetNofGens
Hope this helps.
Pete Lund
-------------------------------------------
WA State Institute for Public Policy
110 East Fifth Avenue, Suite 214
PO Box 40999
Olympia, WA 98504-0999
(360) 586-9436 - voice
(360) 586-2793 - fax
(360) 280-4892 - cell
peter.lund@wsipp.wa.gov
----------------------------------------------------
-----Original Message-----
From: Jonathan Goldberg [mailto:Jonathan_Goldberg@MASTERCARD.COM]
Sent: Monday, February 05, 2001 11:13 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: What do I unquote?
>Try evaluating &GDataset in double quotes, "&GDataset".
>
>On a PC this would also work (GDG names are <datasetname>#<nnn>)
>upcase (memname) like "%upcase(&GDataset)#%"
>
>--
>Richard DeVenezia - SAS Macros and AF Tools
>http://www.devenezia.com
Richard:
Thanks for the suggestion. However, with or without the %unquote it
failed,
generating incorrect quotes in the select statement:
MPRINT(GETNOFGENS): select put(count(*), 1.) into :"oldclusters".count
from
where memname contains '#' and upcase(memname) contains
upcase('oldclusters') ;
305 "oldclusters
____________
22
ERROR 22-322: Expecting a name.
Adding an n, to try to form a name literal, got some of the most unusual
error
messages I've ever seen.
Jonathan
"Jonathan Goldberg" <Jonathan_Goldberg@MASTERCARD.COM> wrote in message
news:862569EA.000A209E.00@mastercard.com...
> I'm writing some code to help me manage GDGs. The platform is V8
running
under
> Solaris 5.6 As one step, I wish to count the number of back
generations
of
> selected GDGs under the assumption that I know what library they're
in.
The
> information needs to be passed into macro variables. I wrote this
macro:
>
> %macro GetNofGens;
> %local GDataset i;
> %let i = 1;
> %let GDataset = %scan(&GDGS, &i);
> %do %while (&GDataset ^=);
> %unquote((&GDataset
> select put(count(*), 1.) into :&GDataset.count
> from dictionary.tables
> where
> memname contains '#' and
> upcase(memname) contains upcase(%str(%')&GDataset%str(%'))
> )
> ;
> %let i = %eval(&i + 1);
> %let GDataset = %scan(&GDGS, &i);
> %end;
> %mend GetNofGens;
>
> The variable GDGS will be set to the list of GDGs in which I am
interested.
> This is intended to be part of a proc sql invocation, and to read from
> dictionary.tables the information I need.
>
> When I first ran this, the generated code looked correct but failed.
This
is
> usually diagnostic of quoting/tokenization problems. I tried %unquote
in
> various places; finally, as above, I unquoted the entire generated
text.
The
> run-time result is:
>
> 290 proc sql;
> 291 %GetNofGens
> MPRINT(GETNOFGENS): select put(count(*), 1.) into :oldclusterscount
from
> dictionary.tables where memname contains '#' and upcase(memname)
contains
> upcase('oldclusters') ;
> MPRINT(GETNOFGENS): select put(count(*), 1.) into :basedatacount
from
> dictionary.tables where memname contains '#' and upcase(memname)
contains
> upcase('basedata') ;
> MPRINT(GETNOFGENS): select put(count(*), 1.) into :statusLcount from
> dictionary.tables where memname contains '#' and upcase(memname)
contains
> upcase('statusL') ;
> MPRINT(GETNOFGENS): select put(count(*), 1.) into :accountscount
from
> dictionary.tables where memname contains '#' and upcase(memname)
contains
> upcase('accounts') ;
> MPRINT(GETNOFGENS): select put(count(*), 1.) into :deptsprtcount
from
> dictionary.tables where memname contains '#' and upcase(memname)
contains
> upcase('deptsprt') ;
> 292 ;
> 293 quit;
>
> This code runs, and counts the data sets correctly. However, the
macro
> variables are *not* set:
>
> WARNING: Apparent symbolic reference OLDCLUSTERSCOUNT not resolved.
> 294
> 295 %put oldclusterscount is &oldclusterscount;
> oldclusterscount is &oldclusterscount
>
> I can kludge are my most pressing requirements by hard-coding; if I
don't
use
> the macro everything works. I can see other ways to approach the
problem.
>
> My questions to you, oh sages of SAS-L, are: what is wrong with *this*
approach;
> and, how can it be fixed?
>
> Jonathan
