Date:         Thu, 15 Feb 2007 22:15:28 +0000
Reply-To:     iw1junk@COMCAST.NET
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ian Whitlock <iw1junk@COMCAST.NET>
Subject:      Re: Macro Resolution Help-Repost
Comments: cc: Ahson umar <ahson_u@YAHOO.COM>

Summary: A short example of macro design without changing  the problem.
#iw-value=2

Ahson,

Here is working code with test code included.  The main changes are in
design (separation of macros, good key word parameters, use of lists and
local variables) and following good coding practices.  Note how the caller
gets control of what to test and where the data are located.

Remember that SQL requires quote marks around character data.

   %macro GSB(lib=work , gsbs = N1 N2 N3 etc., bkns = 1 2 3 etc.);
      %local i gsb ;
      %do i=1 %to &sysmaxlong ;
         %Let GSB=%scan(&gsbs,&i);
         %if %length(&gsb) = 0 %then %goto mexit ;
         %Book_group(lib=&lib, bkns=&bkns, gsb=&gsb)
      %end;
   %mexit:
   %mend GSB;

   %macro Book_group(lib=work, bkns=1 2 3 etc. , gsb=not_allocated);
      %local i bk ;
      %do I=1 %to &sysmaxlong;
         %Let BK=%scan(&bkns,&i);
         %if %length(&bk) = 0 %then %goto mexit ;

         proc sql;
            create table FYR_advertisers1_&GSB&BK as
            select GSB_2004, BOOK_GROUP
                 , ADVERT_1_SIZE_2004
              from &lib..FYR_advertisers
             where TOTAL_ADVERTS_2004=1
               and UPCASE(GSB_2004)="%upcase(&GSB)"
               and BOOK_GROUP ="&BK"
             order by GSB_2004
                 , BOOK_GROUP
                 , ADVERT_1_SIZE_2004
            ;
         quit;
      %end;
   %mexit:
   %mend Book_group;

   option mprint ;
   data FYR_advertisers ;
      input Book_Group $1 GSB_2004 :$8. ADVERT_1_SIZE_2004 :$20. ;
      TOTAL_ADVERTS_2004=1 ;
   cards ;
   1 Gold 15mm
   2 Bronze 30mm
   3 Silver 45mm
   4 Gold 1/4Column
   2 Bronze Double1/4 Column
   3 Silver 45mm
   4 Silver 30mm
   ;
   /* assert
      gold1 and silver3 are not empty
      gold3 and silver1 are empty
   */
   %GSB(gsbs=GOLD SILVER, bkns=1 3)

Ian Whitlock

PS.  Anyone know why the code below wraps when I use FoxFire
to copy it from the listserv?
=================
Date:         Thu, 15 Feb 2007 02:51:58 -0800
Reply-To:     Ahson umar <ahson_u@YAHOO.COM>
Sender:       "SAS(r) Discussion"
From:         Ahson umar <ahson_u@YAHOO.COM>
Subject:      Macro Resolution Help-Repost
In-Reply-To:  <200702150032.l1EKCLBY013007@mailgw.cc.uga.edu>
Content-Type: text/plain; charset=iso-8859-1

Dear All: I have a macro with two loops. I want the macros to resolve as
follows in the sql where clause

GSB= 'GOLD' ......... BK='1' .........

where the sql can then go and pickup the relevent data from the dataset. I
have tried different things but have not come up with a solution. Can
anyone help?

Ahson

Simplified Macro and data

%macro GSB(N1, N2, N3, N4, N5);

%do K=1 %to 2; %Let GSB=&&N&k;

%macro Book_group(M1, M2, M3, M4); %do I=1 %to 2; %Let BK=&&M&I;

proc sql; create table price.FYR_advertisers1_&GSB&BK as select GSB_2004,
BOOK_GROUP, ADVERT_1_SIZE_2004, from price.FYR_advertisers where
TOTAL_ADVERTS_2004=1 and UPCASE(GSB_2004)=&GSB

and BOOK_GROUP =&BK

group by GSB_2004, BOOK_GROUP,ADVERT_1_SIZE_2004; quit;

%end; %mend Book_group; %Book_group(1, 2, 3, 4) %end; %mend GSB; %GSB(GOLD,
SILVER, BRONZE, NOT_ALLOCATED, INSURANCE)

Table FYR_advertisers has the following values Book_Group is a text field

Book_Group GSB ADVERT_1_SIZE_2004 1 Gold 15mm 2 Bronze 30mm 3 Silver 45mm 4
Gold 1/4 Column 2 Bronze Double 1/4 Column 3 Silver 45mm 4 Silver 30mm