BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TedShelly
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

View solution in original post

6 REPLIES 6
ballardw
Super User

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.

TedShelly
Calcite | Level 5

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

Reeza
Super User

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;

Cynthia_sas
SAS Super FREQ

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;


pix_means_xp.pngpix_means_html.png
ballardw
Super User

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;

TedShelly
Calcite | Level 5

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

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
  • 6 replies
  • 3972 views
  • 3 likes
  • 4 in conversation