How to Apply proc format for Blank numeric values

[mgorripati]

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. 

[Ksharp]

It is proc report thing. Plz post it at ODS Report forum.
using style=

options missing='-';
................
proc report ..........
column xx/style={just=center};
...............

[mgorripati]

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,

[Ksharp]

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;

[Reeza]

Proc format;

value custom_fmt

0, . = '-'

other=[percent8.2];

run;

[Ksharp]

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;

[mgorripati]

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,

[jack_CR]

Hi firend...

did you try with this?

OPTION MISSING=' '

It will be set in the beginning of your program. 
it works to me.