BookmarkSubscribeRSS Feed
kevsma
Quartz | Level 8

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;

 

5 REPLIES 5
Kurt_Bremser
Super User

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.

kevsma
Quartz | Level 8

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. 

PaigeMiller
Diamond | Level 26

@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?

--
Paige Miller
kevsma
Quartz | Level 8

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... 

PaigeMiller
Diamond | Level 26

Minor change to the solution above from @Kurt_Bremser 

 

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 845 views
  • 1 like
  • 3 in conversation