Statistics not printing for ODS Proc Freq output

Question

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;

Accepted Answer

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;

Answer 2

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.

Answer 3

3	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 Tolbert 31●1 accept rate: 0%

Statistics not printing for ODS Proc Freq output

Follow this question

Related questions