I want to display data using proc report . I have a numeric column with percent8.2 format . When the data is not available to calculate percentages , the column has BALNK values (I suppose missing numeric supposed to be dot (.) ) . how do i compate blank numeric values ?
I want to display '-' in place of missing data . I have choosen two approaches.
1) I want to use proc format to apply formating for missing data
value zero (default=12) 0='-'
'.'='-';
but, how do i apply format for missing numeric value.
2) using Options missing='-'
The problem with the above approach is that i am not able to center the Dash symbol(-) in my excel report.
it appears to the right of the column.
How can i center '-' symol to the center of the excel cell.
I am using excelxp tagests and proc report to generate report.
Any help is highly appreciated.
It is proc report thing. Plz post it at ODS Report forum. using style= options missing='-'; ................ proc report .......... column xx/style={just=center}; ...............
Hi Xia,
I assume you are talking about using style format in variable DEFINE section of the PROC REPORT OR on the PROC REPORT line ?
I have two version but none are able to center justify the Dash (-) symbol.
1)
options missing='-';
PROC REPORT data= DATASET nofs headline headskip missing split='/'
style(report)=[just=center]
style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9
fontfamily=helvetica fontsize=3 textalign=c font_weight=bold]
style(column)=[just=center];
Column Script_Count Member_Percent ;
DEFINE Script_Count / ANALYSIS FORMAT=comma. CENTER 'Script Counts' style ={tagattr="format:###,###,###"};
EFINE Member_Percent / Analysis CENTER 'Members (%)' ;
RUN:
2)
options missing='-';
PROC REPORT data= DATASET nofs headline headskip missing split='/'
style(report)=[just=center]
style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9
fontfamily=helvetica fontsize=3 textalign=c font_weight=bold];
Column Script_Count Member_Percent ;
DEFINE Script_Count / ANALYSIS FORMAT=comma. CENTER 'Script Counts' style ={tagattr="format:###,###,###"};
EFINE Member_Percent / Analysis CENTER 'Members (%)' style(column)={just=center} ;
RUN:
Thanks,
Very frustrated . A workaround way is making a new CHARACTER variable. Another solution is directly modify its html source code. Proc format; value custom_fmt 0, . = "-" other=[percent8.2]; run; data have; set sashelp.class; age=age*.01; if _n_ in (2:6) then age=.; new_age=put(age,custom_fmt10.); run; ods escapechar='~'; ods tagsets.excelxp file='/folders/myfolders/xx.xml' style=sasweb; proc report data=have nowd; column name sex new_age; define new_age/display style={just=center}; run; ods tagsets.excelxp close;
Proc format;
value custom_fmt
0, . = '-'
other=[percent8.2];
run;
You could make a COMPUTED variable to make a character variable for this: Proc format; value custom_fmt 0, . = "-" other=[percent8.2]; run; data have; set sashelp.class; age=age*.01; if _n_ in (2:6) then age=.; run; ods tagsets.excelxp file='/folders/myfolders/xx.xml' style=sasweb; proc report data=have nowd; column name sex age new_age; define age/analysis noprint; define new_age/computed style={just=center}; compute new_age/character length=10; new_age=put(age.sum,custom_fmt10.); endcomp; run; ods tagsets.excelxp close;
HI Xia,
Thanks for you suggestion. I have done something similar to that for missing numeric values.
DATA A;
SET A;
IF Member_Percent <0 THEN Member_Percent=0;
RUN;
My program never results in a negative percentage so the above would work for me .
proc format ;
value zero (default=12) 0='-' ;
RUN;
Thanks,
Hi firend...
did you try with this?
OPTION MISSING=' '
It will be set in the beginning of your program.
it works to me.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.