topic Re: Replace Blanks with 0 for Analysis Variables in PROC REPORT in ODS and Base Reporting

Replace Blanks with 0 for Analysis Variables in PROC REPORT

tburus — Tue, 04 Aug 2020 04:04:56 GMT

I am creating a cross tabulation table in PROC REPORT and have a couple of columns where there is no data for the cross tab (generated as ANALYSIS variables). As coded it currently displays blanks in these cells. I want to replace it with 0 (or 0% as appropriate). I tried OPTIONS MISSING='0', but this just places a 0, and doesn't match the formatting of all cells.

Code:

proc report data=mfb_ly nowd center;
	where(not missing(mech) and not missing(diag));
	options missing='';
	column mech diag,(nationalweight nationalweight=pct);
	define mech / group 'Activity' center order=freq descending style(column)=[just=l];
	define diag / across 'Diagnosis' order=formatted;
	define nationalweight / analysis SUM 'n' format=comma8.0;
	define pct / analysis PCTSUM format=percent8.0 '%';
	rbreak after /summarize UL OL style(summary)={font_weight=bold};
	compute mech;
    	if _break_ = '_RBREAK_' then mech = 'Total';
	endcomp;
run;

Result:

Re: Replace Blanks with 0 for Analysis Variables in PROC REPORT

mkeintz — Tue, 04 Aug 2020 05:08:01 GMT

I suspect you can make a custom format that uses the regular sas formats you specify, with one wrinkle - format a missing value to a 0, as in:

proc format;
  value mycom
      .=0  other=[comma8.0] ;
  value mypct
      .=0  other=[percent8.0];
run;

followed by a modification of the proc report:

proc report data=mfb_ly nowd center; where(not missing(mech) and not missing(diag)); options missing=''; column mech diag,(nationalweight nationalweight=pct); define mech / group 'Activity' center order=freq descending style(column)=[just=l]; define diag / across 'Diagnosis' order=formatted; define nationalweight / analysis SUM 'n' format=mycom8.0; define pct / analysis PCTSUM format=mypct8.0 '%'; rbreak after /summarize UL OL style(summary)={font_weight=bold}; compute mech; if _break_ = '_RBREAK_' then mech = 'Total'; endcomp; run;