topic Re: Missing crosstab cell values from proc freq in excelXP file in ODS and Base Reporting

Missing crosstab cell values from proc freq in excelXP file

LarryWorley — Tue, 24 Jul 2012 19:08:26 GMT

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.

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

Reeza — Tue, 24 Jul 2012 19:14:44 GMT

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.

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

Cynthia_sas — Tue, 24 Jul 2012 19:19:20 GMT

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

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

Reeza — Tue, 24 Jul 2012 19:22:23 GMT

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.

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

LarryWorley — Tue, 24 Jul 2012 19:22:38 GMT

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

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

LarryWorley — Tue, 24 Jul 2012 19:24:36 GMT

Thanks Cynthia,

Yet another reason to upgrade:-)

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

Reeza — Tue, 24 Jul 2012 19:24:40 GMT

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

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

KRUDEL — Tue, 31 Jul 2012 08:59:26 GMT

Cynthia,

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

Thanks

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

Cynthia_sas — Tue, 31 Jul 2012 15:08:23 GMT

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;