Date:         Tue, 15 Nov 2005 15:36:14 +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: Macro Issue
Comments: To: gsingh24@GMAIL.COM
In-Reply-To:  <1132067400.261977.41490@o13g2000cwo.googlegroups.com>
Content-Type: text/plain; format=flowed

Richie,

Here is a slim version of what I think you are asking for:

%macro States(State = ) ;

Proc Sql ;
Create table Merge as
   Select *
    From A (where state = "&state") as a
             FULL OUTER JOIN
           C (where state = "&state" ) as b
          on  a.State = b.State ;
quit ;


data _Temp ;
set Merge ;

if ( ( VarA not in (. , 0) ) or ( VarB not in (. , 0) ) )  then do ;
  LossRatio = 100 * VarA / VarB ;
end ;
else do ;
  LossRation = . ;
end ;

run ;


proc append
  base = Final
   data = _Temp force ;
run ;

proc sort
data = Final ;
by states ;
run ;

proc datasets ;
delete _Temp ;
run ;

%mend States ;







Toby Dunn





From: Richie <gsingh24@GMAIL.COM>
Reply-To: Richie <gsingh24@GMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Macro Issue
Date: Tue, 15 Nov 2005 07:10:00 -0800


Hi All,

I am trying to write a macro to create a certain SAS data set for 50 US
states. Below is what the code looks like. I am trying to get the final
Data set 'F' to contain values of all the states. But when I run
the code, the values in the dataset(F) are overwritten by the values of
the latest State in the macro. Like in this case it will only have the
values of Fl, not NY, CA, FL simultaneously.


%MACRO STATE(STATES=);

/*Select data by states*/
Data A;
Set B;
Where State = "&states";
Run;

Data C;
Set D;
Where State = "&states";
Run;

/*Many-to-Many merge of tables*/
Proc Sql;
   Create table Merge as
   Select *
   From A as a FULL OUTER JOIN
         C as b
   on    a.State= b.State
;
quit ;

/*calculate ratio*/
Data E;
         SET Merge;
         LossRatio = 100*VarA/VarB;
Run;

/*polulate data by state*/

Data F;
Set E;
by State;
Run;

%MEND STATE;

%STATE (STATES= NY);
%STATE (STATES= CA);
%STATE (STATES= FL);
other states...

RUN;

Thanks,
Richie