topic Re: Need help with creating multiple outputs tables using array in SAS Programming

Need help with creating multiple outputs tables using array

NewToSAS_1019 — Thu, 06 Dec 2012 15:55:19 GMT

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!

Re: Need help with creating multiple outputs tables using array

Reeza — Thu, 06 Dec 2012 15:57:12 GMT

What are you exporting to? Excel?

Re: Need help with creating multiple outputs tables using array

NewToSAS_1019 — Thu, 06 Dec 2012 15:59:44 GMT

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.

Re: Need help with creating multiple outputs tables using array

ballardw — Thu, 06 Dec 2012 16:04:49 GMT

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.

Re: Need help with creating multiple outputs tables using array

NewToSAS_1019 — Thu, 06 Dec 2012 16:41:30 GMT

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

Re: Need help with creating multiple outputs tables using array

ballardw — Thu, 06 Dec 2012 17:59:03 GMT

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;

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.

Re: Need help with creating multiple outputs tables using array

Wizard — Thu, 06 Dec 2012 21:14:18 GMT

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;

Re: Need help with creating multiple outputs tables using array

NewToSAS_1019 — Fri, 07 Dec 2012 16:53:02 GMT

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.