Including Variable or Frequency Name in Title Using Tagsets.ExcelXP

[rballantyne]

First community post ever... Using SAS 9.4 TS Level 1M3. While I greatly appreciate tagsets.excelXP, I'm struggling to capture all the information I see on the SAS HTML output screen.  Specifically I am unable to capture the variable name and frequency variable as a title or even header.  Sample code:

ods listing close ;
ods tagsets.excelxp file = "test.xls"
   options(embedded_titles='yes' embedded_footnotes='yes'
   print_header='yes' print_footer='yes'
   suppress_bylines='yes' sheet_interval='none');
proc ttest data = test alpha = 0.2 cochran plots=none ;
   class file ;
   freq campaign ;
   var cost clicks ;
   title 'Ttest - Test file';
run;
ods tagsets.excelxp close ;
ods listing ;
quit;

The information containing variable name 'cost' exists in the SAS HTML output as:

<div class="c proctitle">The TTEST Procedure</div>
<div class="c proctitle">&nbsp;</div>
<div class="c proctitle">Variable: cost</div>

and the frequency variable 'campaign' exists in the SAS HTML output as:

<div align="left"><table class="proctitle"><tr><td class="l proctitle">Frequency: campaign</td></tr></table>

However I'm at a loss as to how to capture and include this in the tagsets.excelXP output.  My second-tier solution will be to wrap the PROC TTEST in a macro, pass each variable and frequency name as an argument into the title, et c.  Thank you in advance!

[PaigeMiller]

You can add your own TITLE statement to the SAS code for PROC TTEST and then it should say anything you want it to say.

[Reeza]

Its not really clear what you want as output. There's a lot of ways to customize your output to get really nice reports, but you need to provide some more details about what you want and what's not working.

[Cynthia_sas]

Hi:
I agree with the previous postings. You need to supply data if you hope for people to help you.

Also, just because you see the variable name and the frequency name in the HTML output does not mean that you can automatically capture them for use in a different destination. None of the sub-options you've specified will cause what I think you explain that you want.

One thing that SAS macro variables allow you to do is symbolic substitution, where you make "helper" variables that stand as "placeholders" in your code, like this:

Here's a paper that discusses macro basics: https://support.sas.com/resources/papers/proceedings13/120-2013.pdf

Hope this helps. If not, please post some code to test with and show exactly how you intend to "capture" the information you want and how you want it to appear.

Cynthia

[rballantyne]

I'll try again with a sample data set...

data test ;
input file $ campaign clicks cost ;
cards ;
test 15 426447 234524.22
test 15 357663 234324.59
ctrl 15 134346 198709.47
ctrl 15 687967 908784.59
;
run;

ods listing close ;
ods tagsets.excelxp file = "test.xls"
options(embedded_titles='yes' embedded_footnotes='yes'
print_header='yes' print_footer='yes'
suppress_bylines='yes' sheet_interval='none');
proc ttest data = test alpha = 0.2 cochran plots=none ;
class file ;
freq campaign ;
var cost clicks ;
title 'Ttest - Test file';
run;
ods tagsets.excelxp close ;
ods listing ;
quit;

As you can see from running the code above, the variable name appears in the HTML output "Variable: cost." Also the frequency name appears as "Frequency: campaign."  Neither naturally appear at all in the Tagset output.  Specifically Tagsets does not seem to capture all titles naturally included by SAS HTML output.  Yes, I could wrap a macro around PROC TTEST and place the variable and frequency names in the title each time I run the PROC.  However I hoped for a solution that would use Tagsets to capture the variable name appearing all the PROCTITLEs executed behind the scenes as SAS creates the HTML output (right-click on HTML output window, view source, Notepad opens to reveal...):

<div class="c proctitle">The TTEST Procedure</div>
<div class="c proctitle">&nbsp;</div>
<div class="c proctitle">Variable: cost</div>

This problem extends to NPAR1WAY as well.  Probably all PROCs that do not include variable name as tabular output.  I hope that's more clear.  Thank you for taking the time read.

[Reeza]

What version of the tag sets are you using? It'll be in the log after running this code. I suspect you're using an older version and you can update that to solve this issue. I think the latest is X.131

Edit: I tested this and you're correct, for some reason the titles within the proc that specify the variable being analyzed or summary statistics is not being outputted to the xls file. 

However, I did test it with ODS Excel and it worked mostly fine. If you can switch your process to that it'll solve the issue. Otherwise the macro is your best workaround, annoying for sure though. The mostly part is because I'm getting a note out of order, but not sure if you'll get the same or if I have something lingering from a different session or setting. 

I would also consider reporting it to SAS tech support.

FYI - I don't think you wanted the print_header/print_footnotes option because that just puts Yes into them, which isn't what I think you're trying to do. 

From docs:

PRINT_HEADER

None

If there are no titles or embedded titles are on, this value will be used as the header for printing

---

@rballantyne wrote:

I'll try again with a sample data set...

data test ;
input file $ campaign clicks cost ;
cards ;
test 15 426447 234524.22
test 15 357663 234324.59
ctrl 15 134346 198709.47
ctrl 15 687967 908784.59
;
run;

ods listing close ;
ods tagsets.excelxp file = "test.xls"
options(embedded_titles='yes' embedded_footnotes='yes'
print_header='yes' print_footer='yes'
suppress_bylines='yes' sheet_interval='none');
proc ttest data = test alpha = 0.2 cochran plots=none ;
class file ;
freq campaign ;
var cost clicks ;
title 'Ttest - Test file';
run;
ods tagsets.excelxp close ;
ods listing ;
quit;

As you can see from running the code above, the variable name appears in the HTML output "Variable: cost." Also the frequency name appears as "Frequency: campaign."  Neither naturally appear at all in the Tagset output.  Specifically Tagsets does not seem to capture all titles naturally included by SAS HTML output.  Yes, I could wrap a macro around PROC TTEST and place the variable and frequency names in the title each time I run the PROC.  However I hoped for a solution that would use Tagsets to capture the variable name appearing all the PROCTITLEs executed behind the scenes as SAS creates the HTML output (right-click on HTML output window, view source, Notepad opens to reveal...):

<div class="c proctitle">The TTEST Procedure</div>
<div class="c proctitle">&nbsp;</div>
<div class="c proctitle">Variable: cost</div>

This problem extends to NPAR1WAY as well.  Probably all PROCs that do not include variable name as tabular output.  I hope that's more clear.  Thank you for taking the time read.

---