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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: