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;
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.
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.
@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?
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...
Minor change to the solution above from @Kurt_Bremser
proc format;
value asterisks
1<-11 = "***"
0,11-high=[3.]
;
run;
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!
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.
Ready to level-up your skills? Choose your own adventure.