topic Re: ExcelXP tagset and proc means/freq output in ODS and Base Reporting

ExcelXP tagset and proc means/freq output

TedShelly — Wed, 13 Jun 2012 14:07:15 GMT

I often use the ExcelXP tagset to create spreadsheets from proc print. I’d like to do the same for the output from proc means and proc freq. I tried doing this in the usual way but did not get the results I expected.

For proc means, all results were on one row of the spreadsheet. I would have thought it would look much like the table created in HTML.

For proc freq, I got only the first table request of six tables specified in the tables statement. Is there any way to get them all on one sheet?

Appreciate any hints on this.

Ted

Re: ExcelXP tagset and proc means/freq output

ballardw — Wed, 13 Jun 2012 14:47:27 GMT

In response to the second part of your question, the TAGSETS.ExcelXP option you are likely looking for is SHEET_INTERVAL.

This option has five possible values: Table, Page, Bygroup, Proc and None. Try ODS Tabsets.ExcelXP (options (Sheet_interval='None') or (Sheet_interval='Proc') before the call to proc freq.

However I've run into cases where you may still get one table per sheet.

I would send the output from proc means/summary to an output dataset and use proc print to display to get around the behavior.

Re: ExcelXP tagset and proc means/freq output

TedShelly — Thu, 14 Jun 2012 14:52:55 GMT

Thanks. That worked well for proc freq. It did not work for proc means.

For proc means, I'd like to capture the default table that is printed in HTML. If I could get that table as an output dataset I could use proc print. But I can't seem to find the right options for the output statement to create a dataset that looks like the HTML table. Any suggestions on that?

Ted

Re: ExcelXP tagset and proc means/freq output

Reeza — Thu, 14 Jun 2012 16:22:30 GMT

Can you post the code you're using and/or what the excel output you'd like to get?

When I run the following code the results I get are almost identical to what I'd get if I ran the code using HTML as the destination, so I wonder if any of the options you're selecting are messing up the output.

ods tagsets.excelxp file='C:\test.xls' style=journal;

proc print data=sashelp.class;

run;

proc means data=sashelp.class;

class sex;

var weight height;

run;

proc freq data=sashelp.class;

table age sex;

run;

ods tagsets.excelxp close;

Re: ExcelXP tagset and proc means/freq output

Cynthia_sas — Thu, 14 Jun 2012 16:23:07 GMT

Hi:

I'm not sure what you mean by the "default" table that is printed in HTML (see screenshot pix_means_html.png). For example, in the code below, I use SALES, INVENTORY and RETURNS with PROC MEANS and a CLASS statement by REGION. I get all of that output in one sheet using TAGSETS.EXCELXP (see screen shot pix_means_xp.png).

To me, the output in Excel (when the file is opened) looks very like the file that is created for ODS HTML.

Since you did not post any code, I made up some code using SASHELP.SHOES, using PROC FREQ (my program gets both FREQ tables into 1 sheet), PROC MEANS (all my MEANS output is in one sheet) and PROC PRINT. If you create an output dataset (using OUT= or OUTPUT statement) from MEANS (and FREQ) and then send that output dataset to ODS, then you will most likely get a different structure to the look -- because dataset structure from MEANS (and FREQ) are different than report structure. Note that in my code below I do NOT create output datasets with MEANS or FREQ. I just route the REPORT results to ODS TAGSETS.EXCELXP.

cynthia

ods listing close;

title; footnote;

ods html file='c:\temp\freq_means.html' style=sasweb;

ods tagsets.excelxp file='c:\temp\freq_means.xml' style=sasweb

options(sheet_interval='none' sheet_name='Freq');

proc freq data=sashelp.shoes;

tables region product ;

run;

ods tagsets.excelxp options(sheet_interval='table' sheet_name='Means');

proc means data=sashelp.shoes n min mean max sum;

class region;

var sales inventory returns;

run;

ods tagsets.excelxp options(sheet_name='Print');

proc print data=sashelp.shoes;

where region = 'Western Europe';

var region product sales inventory returns;

run;

ods _all_ close;

Re: ExcelXP tagset and proc means/freq output

ballardw — Thu, 14 Jun 2012 16:37:58 GMT

I had forgotten about the appearance of means output. I generally use PROC TABULATE to create table output instead because of control and appearance issues. The following example should give you an idea how to generate something similar to base MEANS output.

proc tabulate data=yourdataset;

var var1 var 2 var3;

table var1 var2 var3, n mean std min max;

run;

Re: ExcelXP tagset and proc means/freq output

TedShelly — Thu, 14 Jun 2012 18:22:11 GMT

Thanks. Proc Tabulate does the job as expected. I'm now a convert to using Tabulate.