The SAS Output Delivery System and reporting techniques

Missing crosstab cell values from proc freq in excelXP file

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 129
Accepted Solution

Missing crosstab cell values from proc freq in excelXP file

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.

Attachment

Accepted Solutions
Solution
‎07-24-2012 03:14 PM
Super User
Posts: 19,792

Re: Missing crosstab cell values from proc freq in excelXP file

Posted in reply to LarryWorley

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.

View solution in original post


All Replies
Solution
‎07-24-2012 03:14 PM
Super User
Posts: 19,792

Re: Missing crosstab cell values from proc freq in excelXP file

Posted in reply to LarryWorley

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.

Frequent Contributor
Posts: 129

Re: Missing crosstab cell values from proc freq in excelXP file

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

Super User
Posts: 19,792

Re: Missing crosstab cell values from proc freq in excelXP file

Posted in reply to LarryWorley

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 Smiley Wink

SAS Super FREQ
Posts: 8,865

Re: Missing crosstab cell values from proc freq in excelXP file

Posted in reply to LarryWorley

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


html_freq_examp.pngxp_freq_examp.png
Frequent Contributor
Posts: 129

Re: Missing crosstab cell values from proc freq in excelXP file

Posted in reply to Cynthia_sas

Thanks Cynthia,

Yet another reason to upgrade:-)

New Contributor
Posts: 4

Re: Missing crosstab cell values from proc freq in excelXP file

Posted in reply to Cynthia_sas

Cynthia,

can you share the script how to create the report that you attached?

Thanks

SAS Super FREQ
Posts: 8,865

Re: Missing crosstab cell values from proc freq in excelXP file

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;

Super User
Posts: 19,792

Re: Missing crosstab cell values from proc freq in excelXP file

Posted in reply to LarryWorley

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1565 views
  • 6 likes
  • 4 in conversation