Incorrect SAS freq table output with ODS Excel

Question

trying to export SAS "proc freq" results to an Excel file (xlsx), using Enterprise guide 7.12 with SAS 9.4 on windows. The following code example :

ODS EXCEL
file='C:\Download\example.xlsx'
STYLE=HtmlBlue
OPTIONS ( sheet_interval="none" sheet_name="Results" );

data example;
input ins_cd$ 1-2 decl_aatrim $ 4-8 prog $ 10-13 compt $ 15-18;
cards;
02 20153 7646 XC12
02 20153 7646 AB02
02 20153 7646 CC13
02 20153 9999     
02 20153 7595 PS03
02 20153 7595 PS04
02 20153 6080 XC12
02 20153 6080 XC15
02 20153 6080 CC18
02 20153 6080 DC08
;

proc sort data=example;
by ins_cd decl_aatrim prog compt;
run;

data example2;
set example;
by ins_cd decl_aatrim prog compt;
if first.prog=1 then do;
test=first.prog;
rank=1;
retain rank 1;
end;
else rank=rank+1;
run;

proc freq data=example2;
tables prog*compt;
run;

ods EXCEL close;

outputs the freq table as expected in the results viewer, with four rows per prog like so :(truncated for less copy paste, and freq row labels values roughly translated ):

                           compt                            
                AB02    CC13    CC18    [...]
prog        
6080    Freq       0       0        1      1       0       0        1      1                            
        Pct     0.00    0.00    11.11   11.11   0.00    0.00    11.11   11.11
        row pct 0.00    0.00    25.00   25.00   0.00    0.00    25.00   25.00
        col.pct 0.00    0.00    100.00  100.00  0.00    0.00    50.00   100.00
7595    Freq       0       0        0       0      0 [...]
[...]

but when the xlsx file produced by ods is opened in Excel, the freq table looks like this:

prog                    compt                               
Freq
Pct
row pct
col.pct     AB02    CC13    CC18    DC08    PS03    PS04    XC12    XC15    Total
6080           0       0       1       1
            0.00       0   11.11 [...]
            0.00    0.00   25.00
            0.00    0.00  100.00
7595           0
            0.00 [...]

and the four cells with freq calculations are merged into one cell and row for each prog.

This http://support.sas.com/kb/32/115.html seems to be related to my problem, but the proposed crosslist solution does not give the wanted output in Excel either. Any ideas? Thanks!

Corrected, thanks. This was from a previous attempt. But the output does not change in xlsx. — Maxime Bélanger

Joe Joe · Accepted Answer · 2016-04-15T14:02:08

This is caused by how PROC FREQ works, and the ODS HTML solution (what you refer to as the results viewer) is no different. Notice that it has:

<td class="r t stacked_cell data"><table width="100%" border="0" cellpadding="7" cellspacing="0">
<tr>
<td class="r t data top_stacked_value">1</td>
</tr>
<tr>
<td class="r t data bottom_stacked_value">11.11</td>
</tr>
</table></td>

Inside each cell - so one main table cell has a mini-table in it with the freq/rowpct/colpct/totalpct in it (or in the case of the above, the two elements on a bottom header).

You can solve this a number of ways. One option is, as Reeza notes in another answer, to use PROC TABULATE.

Another option would be to write your own table template via PROC TEMPLATE; that's how PROC FREQ's crosstab is done, after all; you could look at how they did that and change it, perhaps.

A third option would be to postprocess this output; since the resulting table has all of the data you want, just not in rows, you could easily write a VBA routine to change the format to the desired one.

Incorrect SAS freq table output with ODS Excel

2 Answers