topic Re: proc report call define, replace value with * in SAS Programming

proc report call define, replace value with *

kevsma — Thu, 30 Mar 2023 17:06:38 GMT

Hello, is there a way to replace a value less than 11 to asterisks in proc report? Currently my SAS code works fine but I need to mask a few cell values as they are below 11. Please help, thanks!

ods pdf notoc file="test.pdf" ;
options missing='0';
ODS ESCAPECHAR='^';
 PROC REPORT DATA=final NOWD HEADSKIP HEADLINE SPACING=1 
	STYLE(column)=[background=white fontstyle=roman fontsize=2.5  fontweight=medium  
				  fontfamily='courier new']
	STYLE(header)=[background=white fontstyle=roman fontsize=2.5  fontweight=bold  
				  fontfamily='courier new']
	STYLE(lines)=[background=white fontstyle=roman fontsize=1.9  fontweight=medium  
				  fontfamily='courier new' color=red]
;
 COLUMN rc2 (fy, (( 'WAP' wap) ('SEP-G' sepg)));
 DEFINE rc2/GROUP ORDER=INTERNAL WIDTH=4 FORMAT=$RC2a. 'Regional Center';
 DEFINE fy/ACROSS SPACING=2 'Fiscal Year';
 DEFINE wap/ANALYSIS SUM WIDTH=3 FORMAT=comma10.0 CENTER SPACING=3 'Code 954'; /*this is where i need to mask values*/
 DEFINE sepg/ANALYSIS SUM WIDTH=3 FORMAT=comma10.0 CENTER SPACING=3 'Code 950'; /*this is where i need to mask values*/

 *alternate shading;
 COMPUTE BEFORE rc2;
      i + 1;
      ENDCOMP;
   COMPUTE wap;
      IF mod(i,2) eq 1
         THEN call define(_row_, "style", "STYLE=[background=lightgrey]");
      ENDCOMP;

 BREAK AFTER rc2/SKIP;
 *RBREAK AFTER/ SUMMARIZE;
 compute rc2;
	if rc2 = 'ZO' then do;
		call define(_ROW_,'style', 'style={font_weight=bold}');
	end;
 endcomp;
 *COMPUTE AFTER;
 *rc2='Total';
 *ENDCOMP;

 COMPUTE BEFORE;
 *LINE @3 ' ';
 *LINE @10 110*'-';
 LINE @10 'FY21/22 and FY22/23 State Claims file may be INCOMPLETE due to billing gap.';
 *LINE @10 110*'-';
 *LINE @3 ' ';
 ENDCOMP;

 TITLE1 "^S={fontstyle=roman fontsize=2.5 fontweight=bold fontfamily='courier new'} Work Activity Program (WAP) and Supported Employment-Group (SEP-G)";
 TITLE2 "^S={fontstyle=roman fontsize=2.5 fontweight=bold fontfamily='courier new'} DDS Consumer Count by Regional Center";

footnote1 "^S={fontstyle=roman just=c fontfamily='courier new' fontsize=2
			fontweight=medium color=red} CONFIDENTIAL, NOT FOR DISTRIBUTION OUTSIDE DDS AND REGIONAL CENTER";
footnote2 "^S={fontstyle=roman just=l fontfamily='courier new' fontsize=1.6 
			fontweight=medium} %sysfunc(today(),mmddyy10.)";
footnote3 "^S={fontstyle=roman just=r fontfamily='courier new' fontsize=1.6
			fontweight=medium} DDS Research Section";
 RUN; 
ods pdf close;

Re: proc report call define, replace value with *

Kurt_Bremser — Thu, 30 Mar 2023 17:18:30 GMT

Define a format which displays asterisks for values below 11, and uses the format you use now for numbers 11 and above.

proc format;
value asterisks
  low<-11 = "***"
  11-high=[3.]
;
run;

Untested, posted from my tablet.

Re: proc report call define, replace value with *

kevsma — Thu, 30 Mar 2023 18:00:53 GMT

Thanks! @Kurt_Bremser this works perfectly! May I ask how to keep the value 0? basically 1-10 will be replaced with *, but I still want 0 to be displayed.

Re: proc report call define, replace value with *

PaigeMiller — Thu, 30 Mar 2023 18:02:14 GMT

@kevsma wrote:

Thanks! @Kurt_Bremser this works perfectly! May I ask how to keep the value 0? basically 1-10 will be replaced with *, but I still want 0 to be displayed.

Will there be negative numbers, which should be replaced by *** and zero should remain zero?

Re: proc report call define, replace value with *

kevsma — Thu, 30 Mar 2023 18:06:25 GMT

Hi @PaigeMiller no negative values, all positive or 0, I want 0 to be displayed but 1-10 replaced with *. Also, since my report of count is by column, if a column only has one value replaced with *, I need a second smallest value replaced with ** (but this second smallest value could be larger than 11). Not sure whether this can be done...

Re: proc report call define, replace value with *

PaigeMiller — Thu, 30 Mar 2023 20:31:19 GMT

Minor change to the solution above from @Kurt_Bremser

proc format;
value asterisks
  1<-11 = "***"
  0,11-high=[3.]
;
run;