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: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

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
  • 903 views
  • 1 like
  • 3 in conversation