topic re: Proc Report in ODS and Base Reporting

re: Proc Report

twildone — Tue, 25 Apr 2017 22:46:27 GMT

Hi.....I have 5 datasets and I am trying to create a excel file with multiple worksheets and each worksheet represents a separate client. Each client worksheet should contain 5 stacked tables ( Table 1 from dataset1, Table 2 from dataset2,...., Table 5 from dataset5). If there were 10 unique clients in the datasets, then I would expect to have 10 worksheets and on each worksheet should be 5 tables. I am using ods excelXP. Any suggestions on the best way to create this excel file. Thanks.

Re: re: Proc Report

collinelliot — Tue, 25 Apr 2017 23:20:41 GMT

I would go with ods tagsets.excelXP and then take advantage of the "sheet_name" and "sheet_interval" options.

Set the sheet_interval to "none" at the start of each client and the sheet_name to "Client #1". The five tables will then go on that one tab. Then for the next client, when you change the sheet_name to "client #2" at the start of the next client, your next set of five tables will go on that tab.

But that's just what I'd do.

Re: re: Proc Report

ballardw — Tue, 25 Apr 2017 23:28:51 GMT

Do you have something similar this that creates the individual tables?

proc report data=data1;
where somevariable='Client1';
<report body>;
run;

proc report data=data2;
where somevariable='Client1';
<report body>;
run;

proc report data=data3;
where somevariable='Client1';
<report body>;
run;

proc report data=data4;
where somevariable='Client1';
<report body>;
run;

proc report data=data5;
where somevariable='Client1';
<report body>;
run;

Re: re: Proc Report

collinelliot — Tue, 25 Apr 2017 23:50:22 GMT

This is a demo version of what I explained awkwardly. You can adapt it to work with a macro, but this gives you the basic idea of generating multiple sets of output per tab.

ods tagsets.excelxp file = "&yourPath\DemoOutput.xml"
    options(sheet_name = 'Client 1' sheet_interval = 'none');

    proc print data = sashelp.class;
    run;

    proc print data = sashelp.class;
    run;


ods tagsets.excelxp options(sheet_name = 'Client 2' sheet_interval = 'none');


    proc print data = sashelp.class;
    run;

    proc print data = sashelp.class;
    run;

ods tagsets.excelxp close;

Re: re: Proc Report

ballardw — Tue, 25 Apr 2017 23:53:51 GMT

Here is an example you may be able to follow:

data names;
   input name $;
datalines;
Alfred
Carol
Judy
;
run;

data _null_;
   set names end=lastname;
   if _n_=1 then 
   call execute('ods tagsets.excelxp file="D:\data\sheettest.xml" ;');
   call execute('ods tagsets.excelxp options(sheet_interval="table");');
   call execute('ods tagsets.excelxp options(sheet_interval="none");');
   call execute('ods tagsets.excelxp options (sheet_name="'||name||'" sheet_label=" ");');
   call execute('Proc print data=sashelp.class (obs=1) noobs; where name="'||name||'";run;');
   call execute('Proc print data=sashelp.class (obs=1) noobs; where name="'||name||'";run;');
   if lastname then 
   call execute('ods tagsets.excelxp close;');
run;

Key parts: the _n_ = 1 is so that the ods tagsets.excelxp is created only once.

The two sheet inteval calls are from trial and error that seem to force the tagset to creat a new sheet each time the 'none' gets hit.

The where= clauses use values from the control data set created above.

The data set option end creates a variable, lastname, that is true when the last record in the names data set is processed. So that puts the ODS close statement.

I demonstrate with two calls to proc print to show that the output goes to the same page in the work sheet.

if you have macros or macro variables involved then stop and don't even try this until you show us the code using them. Macro's behave differently than you may expect with call execute.

Note that the Call execute can have lots of lines of code if the body of the report procedures is the same for each repor you can place it

all in one call execute statement.