What is wrong with this code? When I run it, I get the PROC FREQ tables just fine in the SAS output window, but the spreadsheet shows just the table stubs, with no statistics in the body. I have the feeling I must be overlooking something very basic, but it's eluding me this morning.

ODS TAGSETS.EXCELXP FILE="C:\Data\AHS\HADS-MHC\SPREADS\MHC101609.xls" STYLE=JOURNAL2; 
TITLE Housing Cost Burden by Income and Tenure; 
TITLE2 2007 American Housing Survey--Housing Affordability Data System; 
PROC FREQ DATA=HADS.HADS2007; 
     WHERE STATUS='1' AND Tenure ^= '3'; 
     TABLES fmtINCRELAMICat*FMTBURDEN FmtOwnRent*FMTINCRELAMICat*FMTBURDEN / 
          FORMAT=COMMA11.0; 
     WEIGHT Weight; 
RUN; 
ODS TAGSETS.EXCELXP CLOSE;

asked 18 Oct '09, 19:36

Moderator's gravatar image

Moderator ♦♦
323820
accept rate: 44%


Excel can read HTML files. If you ODS HTML statement you should get the output you want.

    ODS HTML FILE="C:\Data\AHS\HADS-MHC\SPREADS\MHC101609.xls" STYLE=JOURNAL2; 
TITLE Housing Cost Burden by Income and Tenure; 
    TITLE2 2007 American Housing Survey--Housing Affordability Data System; 
    PROC FREQ DATA=HADS.HADS2007;
         WHERE STATUS='1' AND Tenure ^= '3';
         TABLES fmtINCRELAMICat*FMTBURDEN FmtOwnRent*FMTINCRELAMICat*FMTBURDEN /
               FORMAT=COMMA11.0;
          WEIGHT Weight; RUN; 
    ODS HTML CLOSE;
link

answered 19 Oct '09, 15:18

SBloom's gravatar image

SBloom ♦
48827
accept rate: 50%

this tagset outputs an XML file with an '*.xls' extension, so right click and send to notepad and you should be able to find your ods output in a bunch of xml markup. If it's not in that, then you may have an outdated tagset or ODS is misbehaving. I suspect ODS listing should be on.

I would also suspect the STYLE=JOURNAL2; statement as excelXP tagset can be kind of picky and not work with some style commands. I would try deleting that and see what happens. You should get the default.

Here's a style that worked for me:

proc template;
define style Styles.whatever;
parent = styles.Printer;
replace fonts /
'TitleFont2' = ("Arial, Helvetica",12pt,Bold)
'TitleFont' = ("Arial, Helvetica",12pt,Bold)
'StrongFont' = ("Arial, Helvetica",11pt,Bold)
'EmphasisFont' = ("Arial, Helvetica",11pt, Bold)
'FixedEmphasisFont' = ("Arial, Helvetica",11pt)
'FixedStrongFont' = ("Arial, Helvetica",11pt,Bold)
'FixedHeadingFont' = ("Arial, Helvetica",11pt,Bold)
'BatchFixedFont' = ("Arial, Helvetica",11pt)
'FixedFont' = ("Arial, Helvetica",11pt)
'headingEmphasisFont' = ("Arial, Helvetica",11pt,Bold )
'headingFont' = ("Arial, Helvetica",11pt,Bold)
'docFont' = ("Arial, Helvetica",11pt);
replace GraphFonts /
'GraphDataFont' = ("Arial",11pt)
'GraphValueFont' = ("Arial",11pt)
'GraphLabelFont' = ("Arial",12pt,Bold)
'GraphFootnoteFont' = ("Arial",12pt,Bold)
'GraphTitleFont' = ("Arial",12pt,Bold);
replace color_list /
'link' = blue
'bgH' = white
'fg' = black
'bg' = _undef_;
replace Table from Output /
background = _undef_
frame = HSIDES
vjust = M
cellpadding = 4pt
cellspacing = 0.75pt
borderwidth = 0.75pt;
replace SystemFooter from TitlesAndFooters /
just = L
font = fonts('docFont');
end;
run;

I also ran into some problems with options in the tagset, and at one point I seemed to have some luck by moving titles outside the block altogether. I think I may also have taken format statements out of the block at one point.

link

answered 22 Oct '09, 18:47

mr_sas's gravatar image

mr_sas
611
accept rate: 0%

edited 22 Oct '09, 18:54

Moderator's gravatar image

Moderator ♦♦
323820

There's an explanation from SAS here: http://support.sas.com/kb/32/115.html

If you add /CROSSLIST to the table statement it works.

link

answered 06 Nov '09, 23:05

Bill%20Tolbert's gravatar image

Bill Tolbert
311
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×21
×7
×2

Asked: 18 Oct '09, 19:36

Seen: 2,900 times

Last updated: 06 Nov '09, 23:05

powered by OSQA