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