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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
Reeza
Super User

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.

LarryWorley
Fluorite | Level 6

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

Reeza
Super User

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

Cynthia_sas
SAS Super FREQ

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
LarryWorley
Fluorite | Level 6

Thanks Cynthia,

Yet another reason to upgrade:-)

KRUDEL
Calcite | Level 5

Cynthia,

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

Thanks

Cynthia_sas
SAS Super FREQ

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;

Reeza
Super User

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.

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