SAS / PROC FREQ TABLES - can I suppress frequencies and percents if frequency is less than a given value?

I'm using tagsets.excelxp in SAS to output dozens of two-way tables to an .xml file. Is there syntax that will suppress rows (frequencies and percents) if the frequency in that row is less than 10? I need to apply that in order to de-identify the results, and it would be ideal if I could automate the process rather than use conditional formatting in each of the outputted tables. Below is the syntax I'm using to create the tables.

ETA: I need those suppressed values to be included in the computation of column frequencies and percents, but I need them to be invisible in the final table (examples of options I have considered: gray out the entire row, turn the font white so it doesn't show for those cells, replace those values with an asterisk).

Any suggestions would be greatly appreciated!!!

Thanks!

dr j

%include 'C:\Users\Me\Documents\excltags.tpl';

ods tagsets.excelxp file = "C:\Users\Me\Documents\Participation_rdg_LSS_3-8.xml"
    style = MonoChromePrinter
    options(
    convert_percentages = 'yes'
    embedded_titles = 'yes'
    );
    title1 'Participation';
    title2 'LSS-Level';
    title3 'Grades 3-8';
    title4 'Reading';
    ods noproctitle;

proc sort data = part_rdg_3to8;
    by flag_accomm flag_participation lss_nm;
run;

proc freq data = part_rdg_3to8;
    by flag_accomm flag_participation;
        tables lss_nm*grade_p / crosslist nopercent;
run;

ods tagsets.excelxp close;

D.Jay: Proc FREQ does not contain any options for conditionally masking cells of it's output. You can leverage the output data capture capability of the ODS system with a follow-up Proc REPORT to produce the desired masked output.

I am guessing on the roles of the lss and grade_p as to be a skill level and a student grade level respectively.

Generate some sample data

data have;
  do student_id = 1 to 10000;
    flag1 = ranuni(123) < 0.4;
    flag2 = ranuni(123) < 0.6;
    lss = byte(65+int(26*ranuni(123)));
    grade = int(6*ranuni(123));

    * at every third lss force data to have a low percent of grades < 3;
    if mod(rank(lss),3)=0 then
      do until (grade > 2 or _n_ < 0.15);
        grade = int(6*ranuni(123));
        _n_ = ranuni(123);
      end;
    else if mod(rank(lss),7)=0 then
      do until (grade < 3 or _n_ < 0.15);
        grade = int(6*ranuni(123));
        _n_ = ranuni(123);
      end;

    output;
  end;
run;

proc sort data=have;
  by flag1 flag2;
  *where lss in ('A' 'B') and flag1 and flag2; * remove comment to limit amount of output during 'learning the code' phase;
run;

Perform the Proc FREQ

Only capture the data corresponding to the output that would have been generated

ods _all_ close;
* ods trace on;
/* trace will log the Output names
 * that a procedure creates, and thus can be captured
 */
ods output CrossList=crosslist;

proc freq data=have;
  by flag1 flag2;
  tables lss * grade / crosslist nopercent;
run;

ods output close;
ods trace off;

Now generate output to your target ODS destination (be it ExcelXP, html, pdf, etc)

Reference output of which needs to be produced an equivalent having masked values.

* regular output of FREQ, to be compare to masked output
* of some information via REPORT;

proc freq data=have;
  by flag1 flag2;
  tables lss * grade / crosslist nopercent;
run;

Proc REPORT has great features for producing conditional output. The compute block is used to select either a value or a masked value indicator for output.

options missing = ' ';

proc format;
  value $lss_report ' '= 'A0'x'Total';
  value grade_report . = 'Total';
  value blankfrq .b = '*masked*' ._=' ' other=[best8.];
  value blankpct .b = '*masked*' ._=' ' other=[6.2];

proc report data=CrossList;
  by flag1 flag2;
  columns
    ('Table of lss by grade'
    lss grade 
    Frequency RowPercent ColPercent
    FreqMask RowPMask ColPMask
    )
  ;
  define lss / order order=formatted format=$lss_report. missing;
  define grade / display format=grade_report.;
  define Frequency / display noprint;
  define RowPercent / display noprint;
  define ColPercent / display noprint;
  define FreqMask / computed format=blankfrq. 'Frequency' ;
  define RowPMask / computed format=blankpct. 'Row/Percent';
  define ColPMask / computed format=blankpct. 'Column/Percent';

  compute FreqMask;
    if 0 <= RowPercent < 10 
      then FreqMask = .b;
      else FreqMask = Frequency;
  endcomp;
  compute RowPMask;
    if 0 <= RowPercent < 10
      then RowPMask = .b;
      else RowPMask = RowPercent;
  endcomp;
  compute ColPMask;
    if 0 <= RowPercent < 10 
      then ColPMask = .b;
      else ColPMask = ColPercent;
  endcomp;
run;

ods html close;

If you have to produce lots of cross listings for different data sets, the code is easily macro-ized.