SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

Reply
N/A
Posts: 0

ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

ods tagsets.ExcelXP path="C:\pathName"
file="fileName &var1..xml"
options(sheet_interval = "Table"
sheet_label = " "
embedded_titles = "yes")
style=Normal;
ods tagsets.ExcelXP options(sheet_name="myTable");
proc tabulate data=...
class
var

table var2,var3*var4*sum/box=" phrase";

table var2,var6*var7*pctsum/box=" phrase2";
.
.
.
table var17,var18*var22*sum/box="Nxt Phrase";
run;
ods tagsets.ExcelXP close;
ods listing;

Question: How do I control the sheet name for each table that Proc Tabulate generates? Is there a method using a macro variable for the Sheet_label option?
Thanks.
SAS Super FREQ
Posts: 8,641

Re: ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

Hi:
The sheet_name option can only go at the beginning of the TAGSETS.EXCELXP invocation or -between- procedure calls. You can't dynamically change the sheet name within one procedure. Given your sheet name value of "MyTable", then you would get worksheets of MyTable, MyTable 2, MyTable 3, MyTable 4, etc, depending on how many tables your code generates.

Given that, you can do something like the program below. Using multiple PROC TABULATE steps (which you could macro-ize to make easier), this syntax results in one worksheet called "MyTable" and a second worksheet called "Wombat".

cynthia
[pre]
%let var1 = XP2_sheetname;

ods listing close;
ods tagsets.ExcelXP path="C:\temp\" (url=none)
file="&var1..xls"
options(sheet_interval = "Table"
sheet_label = " "
sheet_name="MyTable"
embedded_titles = "yes")
style=Normal;

proc tabulate data=sashelp.prdsale;
class country region product;
var actual;
table country,region*actual*sum/box=" phrase";
run;

ods tagsets.Excelxp options(sheet_name="Wombat");

proc tabulate data=sashelp.prdsale;
class country region product;
var actual;
table country,product*actual*pctsum/box=" phrase2";
run;
ods tagsets.ExcelXP close;
ods listing;
[/pre]
N/A
Posts: 0

Re: ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

Thanks. The proc tab code is too random to turn into a macro.
Adding this much "extra" code seems less robust than what a pro would do.

I saw an email from Eric from SAS that I simply could not follow. Inside Proc Template, he mentioned the mvar statement with a "mvar override_sheetname;"
Is there a way to copy the tagsets.ExcelXP to allow for naming the sheet name?

Another method that is close has the sheet_interval = "bygroup" and the sheet name is changed every time the by group changes. But my original proc tab code isn't organized in a by group manner. I was hoping sheet name could be set to a macro variable that changed with each table statement within the proc tab. Even better would be a macro variable that read the /box= option on the table statement and used that phrase for the sheet name.

At what point does one run out of workaround ideas and simply need to code it in and move on?
SAS Super FREQ
Posts: 8,641

Re: ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

Hi:
The problem with a macro variable override for sheet_name is still the same issue, you could not issue a %LET between TABLE statements, only between full procedures, each with a step boundary.

If you can't macro-ize the full TABULATE, you could at least macro-ize the proc tabulate statement and the class and var statements, since those would be the same for every table statement (as you have things coded now).

Sheet_interval for BYGROUP would come close, but I suspect your TABULATE won't lend itself to BY group processing. The macro variables in the GLOBAL symbol table get resolved at macro trigger resolution phase, which happens before compile phase...conceptually like this:

[pre]
Code gets submitted. Code is submitted line by line until a step boundary is reached.
|
V
All macro triggers (%LET, &macvar, %macpgm) get resolved for each step
ONE time at code compile time -- this would not allow for
the dynamic resolution that you hoped for.)
|
V
After all macro triggers are resolved, in a complete step, the step is
conceptually sent to the compiler and the step is executed.
|
V
This cycle repeats for EVERY step (usually every procedure).
[/pre]


There is a way with a DATA step program to communicate with the GLOBAL symbol table, but the issue here is that any macro variables created in a DATA step program will not be "usable" until after the DATA step boundary has happened.

The fact is that the macro variable &override_sheetname is already available for use. See the code below:
[pre]

ods tagsets.ExcelXP path="C:\temp\" (url=none)
file="alt2.xls"
options(sheet_interval = "Table"
sheet_label = " "
embedded_titles = "yes")
style=Normal;

%let override_sheetname = First;

proc tabulate data=sashelp.prdsale;
class country region product;
var actual;
table country,region*actual*sum/box=" phrase";
run;

%let override_sheetname = Second;

proc tabulate data=sashelp.prdsale;
class country region product;
var actual;
table country,product*actual*pctsum/box=" phrase2";
run;
ods tagsets.ExcelXP close;
ods listing;

%let override_sheetname=;

[/pre]

But this technique only works because the %LET for the first TABULATE is resolved and then used BEFORE the %LET for the second TABULATE full step is compiled, executed and used.

When you have a %LET -inside- a TABULATE step, such as between TABLE statements, there is a macro variable timing issue. The second or last %LET statement will be the one used for the global symbol table. The timing of macro variable resolution is just different for complete steps versus "within" a step.

To prove this to yourself, you can run this code which uses a %LET statement within a single TABULATE step.
[pre]
** alternate try;
ods tagsets.ExcelXP path="C:\temp\" (url=none)
file="badtry.xls"
options(sheet_interval = "Table"
sheet_label = " "
embedded_titles = "yes")
style=Normal;

%let override_sheetname = First;

proc tabulate data=sashelp.prdsale;
class country region product;
var actual;
table country,region*actual*sum/box=" phrase";

%let override_sheetname=Second;

table country,product*actual*pctsum/box=" phrase2";
run;
ods tagsets.ExcelXP close;
ods listing;
%let override_sheetname=;
[/pre]

With the above syntax, you will get Second and Second2 as the sheet names because the second %LET is within the same step as the first %LET. At this point, you are out of options. Macro variables don't work the way you want. I'd recommend that you code full TABULATE steps with a SHEET_NAME between each procedure step and move on.

cynthia
Contributor
Posts: 69

Re: ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

A belated response.

Macro loops are your best friend, especially when you want PROCs to do things out of the ordinary.

It is worth learning how to set up a macro loop....I use them all the time to generate hundreds of ExcelXP reports using PROC REPORT on different sort key values (e.g. department code). These end up being print-ready worksheets that have titles, frozen headers, Excel formatting, colours, page numbers (page X of Y), run date/time, etc., with all columns scaled to fit the page.

The same approach can be used on PROC TABULATE. Put the procedure inside a macro routine, then call the routine with parameters that are set in a macro loop. Then change the sheet name each to suit. You can just use the ODS ExcelXP option statement here.

Cheers
Bosch
New Contributor
Posts: 4

Re: ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

 

 

New Contributor
Posts: 4

Re: ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

[ Edited ]

Hi Cynthia_sas,

 

Thanks for your response though. I do agree with you.

 

But here my whole idea is each individual value in the By group should have a unique table within the same sheet (sheet_name='sheet 1') one below one, not in a different sheet. If the input dataset changed, then the sheet_name also should change. So, here two things need to handle.

 

The first one: within the one sheet each value(sashelp.class Age=11,12,14...etc.)  should have individual table.

       Second:  when the input dataset changed, then the proc print is on sheet 2 within the same file.

 

Example: In sashelp.class Age=11 group table should populate first. On the same sheet below Age=12 group table with title.

 

So, I am subsetting age variable in sashelp.class through macro loops, instead of multiple PROCs. I tried with ODS HTML with macro loops and its working. But sheet_name is not coming.

 

Below code I am using:  

 

%macro xls(sheet=);

title; footnote;

ods listing close;
os tagsets.excelXP file="/path/report.xls" new_file='none';
%do i=11 %to 14;
ods tagsets.excelXP options(sheet_intervals='none' sheet_name="&sheet.");
title "&i.";
proc print data=sashelp.class noobs;
where age=&i.;
run;
%end;
ods _all_ close;
%mend;


%xls(sheet=sheet1 );

 

Please suggest.

 

Thank you in advance.

SAS Super FREQ
Posts: 8,641

Re: ODS tagsets.ExcelXP control SheetName of each table in Multi table Proc

Hi:

  I rarely look in this forum, since I mainly teach the ODS and Base Reporting classes, along with the stored process classes. However, you posted a similar question in a different forum and I posted a response over there with some sample code. Please look at the use of SHEET_NAME='NONE' that I posted here:
https://communities.sas.com/t5/ODS-and-Base-Reporting/Display-All-BY-groups-in-same-ODS-EXCEL/m-p/32... which shows how to do what I think you want to do.

  The BY group is age based on SASHELP.CLASS. If you want the title to reflect the name of the BY group, you need to use #BYVAL in the TITLE statement. However, the titles will not show in the sheet unless you use the embedded_titles='yes' suboption. I am still not convinced you need a macro solution, since it is possible to do everything you seem to want with suboptions and BY group processing.

 
cynthia

byval_age.png

Post a Question
Discussion Stats
  • 7 replies
  • 7802 views
  • 0 likes
  • 4 in conversation