Can anyone think of a way to avoid those decimal places in the Frequencies column?
From the Log
NOTE: The FORMAT= option has no effect in a CROSSLIST table.
Consider that you are using FREQ to only count and computed percentages within a group. That is a very small subset of FREQs analytic capability but you don't have complete control over the presentation.
Suppose you preprocess the data a little and use a different procedure that has more control over presentation. That would be either proc REPORT or TABULATE, followed up by PRINT.
Consider the case of TABULATE. You will need an analytic variable for weighted statistical processing (tabulate does not do weighted N). I use the surrogate variable UNITY (=1) to replicate weighted N counting. You also want to have the rows ordered by FREQs 'freq' which corresponds to a weighted analysis result in TABULATE. TABULATE only provides for ordering according to classification variables, you can force a custom order using / ORDER=DATA if your pre-processing sorts the data in the desired order.
NOTE: Tabulate can NOT produce a table of the NLEVELS counts that FREQ does. That output can be from a FREQ run-through that selects ODS table NLEVELS.
Example:
data have;
input tier1 $ tier2 $ impact;
datalines;
P A 10.25
P A 10.75
P A 11.33
P A 13.11
P B 9.80
P B 10.33
P B 11.90
P B 12.15
P B 13.42
P C 0.00
P D 10.55
P . 11.11
run;
Proc FREQ with uncontrollable formatting of frequency values
title "Freq, weighted by IMPACT";
tables
tier1 * tier2 /* two-way crosstabulation, tier1 down the table, tier2 across the table */
/ crosslist /* arrange output so across values (tier2) appear as an adjacent down list */
nocum /* suppress cumulative counts from output, only applies to ONE WAY tables though */
nocol /* suppress column percent from ouput */
norow /* suppress row percent from ouput */
format=comma12. /* format for tier2 frequency values, is NOT applied in crosslist mode -- come on SAS, fix that */
sparse /* only applied if LIST or OUT= also in effect */
missprint /* deal with missing table var values, either: */
/* - as bin value when proc option MISSING in effect */
/* - as a summary line below table (default) */
;
weight impact / zeros;
run;
OUTPUT from FREQ with unwanted decimals in weighted frequency
SELECT NLEVELS and Pre-process data with TABULATE controlling formatting
proc sql;
create table have2 as
select *, 1 as unity, sum(impact) as weighted_freq from have
group by tier1, tier2
order by weighted_freq desc;
quit;
ods escapechar = '^';
ods select NLevels;
ods noproctitle;
proc freq data=have nlevels ;
title "Freq, just NLEVELS";
tables tier1 * tier2;
run;
proc tabulate data=have2;
title "TABULATE, UNITY weighted by IMPACT";
class tier1 tier2 / order=data;
weight impact;
var unity;
table
tier1 * tier2 ALL
,
(
n="Unweighted^{newline}Freq"
unity *
( sum='Weighted^{newline}Freq'
sum='Weighted^{newline}Freq^{newline}(comma12.)' * f=comma12.
colpctsum='%'
colpctsum='% (4.)'*f=4.
)
) * [style=[textalign=center]]
/
nocellmerge
;
run;
Output
... View more
