Date: Mon, 20 Aug 2007 17:30:31 +0000
Reply-To: toby dunn <tobydunn@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: toby dunn <tobydunn@HOTMAIL.COM>
Subject: Re: invoking a macro with nested macros within a sas proc
In-Reply-To: <OF9BA5BC2B.2AA40794-ON8525733D.005A1230-8525733D.005F153C@freddiemac.com>
Content-Type: text/plain; format=flowed
Rushi ,
What you need to do is forget about SQl and write a Pure Macro solution.
This will get you started, just modify it where you need too:
%macro makesqllist( DataIn = , PreFix = , ExcludeVars = ) ;
/******************************************************************/
/** Macro Name : MakeSQLList **/
/** **/
/** Purpose : To create list for Select Clause for a SQL **/
/** statement **/
/** **/
/** Parameters : DataIn ~ Input Data Set Whos Variables You **/
/** The list to be made of. **/
/** **/
/** Prefix ~ Characters you want to prefix each **/
/** element in the created list. This **/
/** feature allows the user to prefix **/
/** element with the data sets alias. **/
/** **/
/** ExcludeVars ~ Is a list if variable names which**/
/** may be in the data set but which **/
/** user wants excluded from the **/
/** created list. **/
/** **/
/** Macro Type : Function Style **/
/** **/
/** Create By : Toby Dunn **/
/******************************************************************/
%Local DSID I Var Close List ;
%Let Dsid = %Sysfunc( Open ( &DataIn , IS ) ) ;
%Do I = 1 %To %Sysfunc( Attrn( &Dsid , Nvars ) ) ;
%Let Var = %Sysfunc( VarName( &Dsid , &I ) ) ;
%If ( %SysFunc( IndexW( %Upcase( &ExcludeVars ) , %Upcase( &Var ) ) ) = 0 )
%Then %Do ;
%Let List = &List &PreFix&Var ;
%End ;
%End ;
%Let Close = %Sysfunc( Close( &Dsid ) ) ;
%SysFunc( TranWrd( &List , %Str( ) , %Str( , ) ) )
%mend makesqllist ;
Toby Dunn
Two wrongs are only the beginning.
Success always occurs in private and failure in full view.
Experience is something you don't get until just after you need it.
From: Rushi Patel <rushi_patel@FREDDIEMAC.COM>
Reply-To: Rushi Patel <rushi_patel@FREDDIEMAC.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: invoking a macro with nested macros within a sas proc
Date: Mon, 20 Aug 2007 13:18:33 -0400
Hello SAS-L,
I have a broad Macro Design as well as a specific code question. The core
issue that I am having trouble with is "how to code nested macros that
uses PROC SQL, within another macro that is invoked within a SAS
procedure?". I attempt to summarize the issue below...
Desirabled Target -
I am trying to automatically create a bunch of formats using PROC FORMAT
statement. Macro "%MAC1 ( VARLIST = )" will write the code required for
the statement PROC FORMAT to create formats.
%MACRO MAC1 ( VARLIST = ) ;
%nested1 ( input_parameters = ) ;
<some logic>
%let final_code = <.................................> ;
%MEND MAC1 ;
%MAC1 (VARLIST = VAR1 VAR2...............) ;
PROC FORMAT ;
&final_code ;
RUN;
The macro string "final_code" contains the required statements ( value
var1 10-20 = '1'
21-30 = '2' ;
value var2 100 - 200 = 'a'
......... )
VARLIST specifies the variables of interest and the control variables
required to execute the macro appropiately. This design (call it design 1)
works perfect and I get the desired output with the desired automation.
However, I want to set up design in the following manner ---
PROC FORMAT ;
%MAC1 (VARLIST = ) ;
RUN ;
(lets call this design 2)
This differs from design 1 because here I can write formats for individual
variables value var1......................; iterate the macro and again
write code value var2....................and so on. I don't need to save
the entire code string in a macro variable (like &final_code). This is a
desirable approach to avoid making the "final_code" macro string too long
(beyond the 65000 character length or similar capacity of macro strings).
In attempting to do code design 2, the issue that I am trying to overcome
is with nested macros. "%nested1" invoked within %MAC1 uses a PROC SQL
NOPRINT; SELECT INTO structure to populate some global macro variables
from a permanant dataset and these global variables are used in %MAC1 and
other nested macros. I cannot use design 2, because %MAC1 itself is
invoked within a SAS PROCEDURE (PROC FORMAT) and as soon as the QUIT
statement of PROC SQL within "%nested1" is encountered PROC FORMAT stops
executing.
I would appreciate if someone can provide me an appropiate direction on
how to avoid this and achieve design 2.
Included below is some code. The code is not complete but should provide
some clarity about what I attempt to describe above.
/************************* The nested Macro
******************************************* ;
%macro parm_est ( cnt = ,
string = ) ;
%local ii cd ;
%put &cnt &string ;
%let ii = 1 ;
%let cd = ;
%do %while (&ii <= &cnt) ;
%let cd = &cd alpha&ii ;
%put &cd ;
%let ii = %eval(&ii + 1) ;
%end ;
%global &cd ;
%let ii = 1 ;
proc sql noprint ;
%do %while (&ii <= &cnt) ;
%let cond = %scan(&string,&ii) ;
select score into: alpha&ii
from _out1
where _name_ = "%upcase(&cond)" ;
%let ii = %eval(&ii + 1) ;
%end ;
quit ;
%mend parm_est ;
Design 1: -
%macro mac1 ( ) ;
.........
.......
........
%parm_est (cnt = abc ,
string = xyz ) ;
...............
%let final_code = <something> ; /********* this takes
everything in one macro string **************** /
%mend mac1 ;
proc format ;
&final_code ;
run ;
Design 2:-
%macro mac1 ( ) ;
.........
.......
........
%parm_est (cnt = abc ,
string = xyz ) ;
...............
&final_code /*************** here a macro
variable is resolved a the time of invokation of %mac1 *****************/
%mend mac1 ;
proc format ;
%mac1 ( ) ;
run ;
_________________________________________________________________
Puzzles, trivia teasers, word scrambles and more. Play for your chance to
win! http://club.live.com/home.aspx?icid=CLUB_hotmailtextlink
