Need help with creating multiple outputs tables using array

[NewToSAS_1019]

Hi, I am trying to create multiple outputs from one source file by filtering on 2 variables: STATE and PLAN. Basicaly I want to crate some kind of array or Do Loop statement to help me to generate 50 outputs, 1 for each state

Here is what I have for one STATE and Plan:

Data TX_A;

Set Work.ratechag_test;

Keep State Plan Ratestru Date RatChang;

Where State = 'TX' and Plan = 'A';  --> Want to change it to 2 variables and loop through differnet state and plans

Run;

PROC Export Data = WORK.TX_A

anyone can help? Thanks!

[Reeza]

What are you exporting to? Excel?

[NewToSAS_1019]

ultimately I probably need to do more formating on WORK.TX_A before I export it to PDF document such as inserting header and footer. I haven't got that far yet.

[ballardw]

Do all of your states have the same set of plans? If not loops may not be optimal as you'll likely get some errors.

For PDF you may want to look at PROC Print instead of Export as Title and Footnote statements will make it easier to get headers and footers.

[NewToSAS_1019]

Yes, all states have the same plans in this example.

[ballardw]

Something like this if you actually need to create multiple datasets:

%macro StatePlan(State,Plan);

Data &State._&Plan;

   set Work.ratechag_test (Keep= State Plan Ratestru Date RatChang

                                     where =(State = "&State" and Plan = "&Plan"));

Run;

/* your output can go here once you are ready*/

%mend;

data _null_;

   do state='AK','AL','TX';

       do Plan = 'A','B';

            call Execute ('%StatePlan('||State||','||Plan||')');

       end;

    end;

run;

Alternative could be something like this if you want to see the output:

%macro StatePlan(State,Plan);

ODS PDF file="C:\yourfoldername\&State._&plan..pdf";

title "Report for &state using &plan";

proc print data=Work.ratechag_test (where =(State = "&State" and Plan = "&Plan"))

                noobs label;

    var Ratestru Date RatChang;

run; title;

%mend;

I didn't include the state and plan here as the title could contain that information.

[Wizard]

I would not use macros for this unless the file names are important.  The spliting of information can be done using BYGROUP processing.   Here is some code that will produce most of what is needed, only the names of the output files may not be usable.  In my example they are StatePlan.1.pdf, StatePlan.2.pdf, ... StatePlan.?.pdf

data test_states;          /* Build test dataset */
   do state='AK','AL','TX';
       do Plan = 'A','B';
       do Ratestru= 'x','y','z';
             Date=today();
             RatChang=5.33;
          output;
    end;
       end;
   end;
run;

proc sort data=test_states;
     by state plan;
run;

ODS PDF file="C:\temp\StatePlan.1.pdf" newfile=bygroup; 

options nobyline;

title "Report for #byval1 using Plan #byval2";
proc print data=Work.test_states noobs label;
     by state plan;
    var Ratestru Date RatChang;
run;

ods pdf close;

[NewToSAS_1019]

Oh my goodness! so many replies THank you all, I will look into this and see whether if I can figure it out!!! There are some advanced coding here that I have never seen before.