BookmarkSubscribeRSS Feed
twildone
Pyrite | Level 9

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.

4 REPLIES 4
collinelliot
Barite | Level 11

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.

collinelliot
Barite | Level 11

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;
ballardw
Super User

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;
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 803 views
  • 1 like
  • 3 in conversation