Grouping plots and tables using a BY statement in SAS

Question

I am using a BY statement with both proc boxplot and proc report to create a plot and a table for each level of the BY variable. As is, the code prints all the plots and then prints all of the tables. I would like it to print the plot and then the table for each level of the By variable (so the ouput would alternate between a plot and a table). Is there a way to do this?

This is the code I currently have for the plots and tables-

proc boxplot data=study;
plot Lead_Time*Study_ID/ horizontal;
by Project_Name;
format Lead_Time dum.;
run;

proc report data=study nowd;
column ID Title Contact Status Message Audience Priority;
by Project_Name;
run;

Thank You!!

Answer 1

Unfortunately, I don't think the ODS (Output Delivery System) can interleave outputs from procedures. You will need to use a macro to loop over all the by variables and call BOXPLOT and REPORT for each one.

Something like this:

%macro myreport();
%let byvars = A B C D;
%let n=4;

%do i=1 %to &n;
   %let var = %scan(&byvars,&i);
   proc something data=have(where=(byvar="&var"));
   ...;
   run;

   proc report data=have(where=(byvar="&var"));
   ....
   run;
%end;
%mend;

%myreport();

Obviously you need to change this to fit your needs. There are plenty of examples on Stackoverflow of it. Here is one: looping over character values in SAS

Answer 2

This is in principle possible using PROC DOCUMENT and the ODS DOCUMENT output type. It's not exactly easy, per se, but it's possible, and has some advantages over the macro option, although I'm not sure sufficient to recommend its use. However, it's worth exploring nonetheless.

First off, this is largely guided (including, coincidentally, using the same dataset!) by Cynthia Zender's excellent tutorial, Have It Your Way: Rearrange and Replay Your Output with ODS DOCUMENT, presented during the 2009 SAS Global Forum. She initially describes a GUI method of doing this, but then later explains it in code, which would clearly be superior for this sort of thing. Kevin Smith covers similar ground in ODS DOCUMENT From Scratch, from 2012's SGF, though Cynthia's paper is a bit more applicable here (as she covers the exact topic).

First, you need to generate all of your results. Order here doesn't matter too much. I generate a sample of SASHELP.PRDSALE that is sorted appropriately by country.

proc sort data=sashelp.prdsale out=prdsale;
by country;
run;

Then, we generate some tables; a proc means and a sgplot. Note the title uses #BYVAL1 to make sure the title is included - otherwise we lose the useful labels on the procs!

title "#BYVAL1 Report";


ods _all_ close;
ods document name=work.mydoc(write);
proc means data=prdsale sum;
 by country;
 class quarter year;
 var predict;
run;

proc sgplot data=prdsale;
 by country;
 vbar quarter/response=predict group=year groupdisplay=cluster;
run;

ods document close;
ods preferences;

Now, we have something that is wrong, but is usable for what you actually want. You can use the techniques in Cynthia or Kevin's papers to look into this in detail; for now I'll just go into what you need for this purpose.

It's now organized like this, imagining a folder tree:

\REPORT\MEANS\COUNTRY\

What we need is:

\REPORT\COUNTRY\MEANS

That's easy enough to do. The code to do so is below. Obviously, for a production process this would be better automated; given the input dataset it should be trivial to generate this code. Note that the BYVALs increment for each by value, so CANADA is 1 and 4, GERMANY is 2 and 5, and USA is 3 and 6.

proc document name=work.mydoc_new(write);
 make CANADA, GERMANY, USA;   *make the lower level folders;
 run;

 dir ^^;  *Go to the bottom level, think "cd .." in unix/windows;
 dir CANADA;  *go to Canada folder;
 dir;         *Notes to the Listing destination where we are, not that important;
 copy \work.mydoc\Means#1\ByGroup1#1\Summary#1 to ^;  *copy that folder from orig doc to here;
 copy \work.mydoc\SGPlot#1\ByGroup4#1\SGPlot#1 to ^; *^ being current directory, like '.' in unix/windows; 

*You could also copy \ByGroup1#1 and \Bygroup4#1 without the last level of the tree. That would give a slightly different result (a bit more of the text around the table would be included), so do whichever matches your expectations.;

**Same for Germany and USA here. Note that this is the part that would be easy to automate!; dir ^^; dir GERMANY; dir; copy \work.mydoc\Means#1\ByGroup2#1\Summary#1 to ^; copy \work.mydoc\SGPlot#1\ByGroup5#1\SGPlot#1 to ^;

 dir ^^;
 dir USA;
 dir;
 copy \work.mydoc\Means#1\ByGroup3#1\Summary#1 to ^;
 copy \work.mydoc\SGPlot#1\ByGroup6#1\SGPlot#1 to ^;



run;
quit;  *this is one of those run group procedures, need a quit;

Now, you only have to replay the document to get it out the right way.

proc document name=mydoc_new;
 replay;
 run;
quit;

Tada, you have what you want.

Answer 3

If you're going to run the procs once per by value, that's pretty easy. Create a macro to run just one instance, then use proc sql to create a call for each instance. That is entirely dynamic, and could be easily adjusted to allow for other options such as multiple by variables, levels, etc.

Given a single by value:

*Macro that runs it once;
%macro run_reports(project_name=);
  title "Report for &project_name.";
  proc boxplot data=study;
   plot Lead_Time*Study_ID/ horizontal;
   where Project_Name="&project_name.";
   format Lead_Time dum.;
  run;

  proc report data=study nowd;
   column ID Title Contact Status Message Audience Priority;
   where Project_Name="&project_name.";
  run;
%mend run_Reports;

*SQL pull to create a list of macro calls;
proc sql;
select distinct cats('%run_Reports(project_name=',project_name,')')
  into :runlist separated by ' '
  from study;
quit;

&runlist.;

Turn options symbolgen; on to see what the runlist looks like, or look at your output window (or results window in 9.3+). When you're running this in production, add noprint to proc sql to avoid generating that table.