topic Format of Proc Freq output in SAS Programming

Format of Proc Freq output

NKormanik — Sun, 31 May 2020 05:42:08 GMT

Please see the output image below:

For some reason in the Frequency column I am getting lines with decimal point and number. xxxxx.4 for instance.

The following is the code used to generate what's shown above:

proc freq
data=sas_3.variable_values_separate_runs nlevels order=freq page;
where Indicator = "i_21504a";
tables
indicator * range
/ nocum nocol norow sparse crosslist missprint format=15.0;
weight number_weighted_yes / zeros;
run;

Can anyone think of a way to avoid those decimal places in the Frequencies column?

Additionally, can anyone suggest a way of showing ONLY ONE decimal place in the Percent column?

Any help greatly appreciated.

Nicholas Kormanik

Re: Format of Proc Freq output

Shmuel — Sun, 31 May 2020 06:04:59 GMT

Try by adding next statement to the proc, just before the RUN;

format count 8.;

or 

format _freq_ 8.;

Re: Format of Proc Freq output

NKormanik — Sun, 31 May 2020 09:39:00 GMT

Managed to get Frequency to format with NO decimal places. By using list, as opposed to crosslist.

Still can't get Percent to a single decimal digit. No way no how.

Ought to be easier....

Re: Format of Proc Freq output

Shmuel — Sun, 31 May 2020 13:29:06 GMT

Is the weight variable NUMBER_WEIGHTED_YES an integer?

Shouldn't weight fraction create, in some cases, a counting fraction?

Re: Format of Proc Freq output

Watts — Sun, 31 May 2020 15:47:53 GMT

You can modify the formats in PROC FREQ tables by using PROC TEMPLATE.

There are some examples in this paper by Kathryn McLawhorn:

Tables and Graphics that will FREQ You Out.

See page 3 for an example of changing the format of Percent in a one-way or LIST table.

Re: Format of Proc Freq output

RichardDeVen — Sun, 31 May 2020 16:47:04 GMT

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