Hi All,
I am attempting to write cross-tab tables from proc Freq to excel file using excelXP tagset. I see the table outline in Excel but do not see the cell results.
You can try the following code to generate this:
ods listing close;
title; footnote;
ods html file='c:\temp\crosstabs.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\corsstabs.xml' style=sasweb
options(sheet_interval='none' sheet_name='Freq');
proc freq data=sashelp.shoes;
tables region*product;
run;
ods _all_ close;
The generated files are attached:
Thanks for your help.
http://support.sas.com/kb/32/115.html
The PROC FREQ crosstab table (TABLES A*B) now has a table template to define it. This adds functionality for traffic lighting and format control much like what was available in previous releases with PROC TABULATE and PROC REPORT, and for one-way tables created by PROC FREQ (TABLES A B).
However, the underlying code for this table uses stacked columns which is not currently supported by the EXCELXP tagset. A crosstab table directed to Excel with the EXCELXP tagset will show the row and column headers, but no data.
To circumvent the problem, use the CROSSLIST option on the TABLES statement so that the crosstab tables come out as simple tables. For example:
TABLES A*B / CROSSLIST;
You can also create Excel files using the MSOFFICE2K tagset which can handle stacked tables.
http://support.sas.com/kb/32/115.html
The PROC FREQ crosstab table (TABLES A*B) now has a table template to define it. This adds functionality for traffic lighting and format control much like what was available in previous releases with PROC TABULATE and PROC REPORT, and for one-way tables created by PROC FREQ (TABLES A B).
However, the underlying code for this table uses stacked columns which is not currently supported by the EXCELXP tagset. A crosstab table directed to Excel with the EXCELXP tagset will show the row and column headers, but no data.
To circumvent the problem, use the CROSSLIST option on the TABLES statement so that the crosstab tables come out as simple tables. For example:
TABLES A*B / CROSSLIST;
You can also create Excel files using the MSOFFICE2K tagset which can handle stacked tables.
Reeza,
Thanks for your quick response.
BTW, When I tried searching for support notes, I did not see that note.Guess I need some practice in searching the SAS web.
Larry
Use google, with site:support.sas.com
ie search proc freq tagsets.excelxp site:support.sas.com
But search results from google are different for everyone and I'm on here way too much
And, with SAS 9.3, the code you posted (with only the FILE= changed to crosstabs.xml instead of corsstabs.xml) produces the output shown in the screen shots (without using the CROSSLIST option). The version of TAGSETS.EXCELXP is:
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011).
My output was created in SAS 9.3 and viewed with Excel 2010.
cynthia
Thanks Cynthia,
Yet another reason to upgrade:-)
Cynthia,
can you share the script how to create the report that you attached?
Thanks
Hi:
I used the OP's originally posted code, using PROC FREQ, (with FILE= changed). My code was run in SAS 9.3 and the files were opened with Excel 2010.
cynthia
ods listing close;
title; footnote;
ods html file='c:\temp\crosstabs.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\crosstabs.xml' style=sasweb
options(sheet_interval='none' sheet_name='Freq');
proc freq data=sashelp.shoes;
tables region*product;
run;
ods _all_ close;
It does actually run though, even though sas hasn't updated the note. Perhaps check with tech support, regarding your version of SAS and tagsets.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.