The SAS Output Delivery System and reporting techniques

How to Apply proc format for Blank numeric values

Reply
Contributor
Posts: 27

How to Apply proc format for Blank numeric values

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. 

Grand Advisor
Posts: 9,315

Re: How to Apply proc format for Blank numeric values

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

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

Contributor
Posts: 27

Re: How to Apply proc format for Blank numeric values

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,

 

Grand Advisor
Posts: 9,315

Re: How to Apply proc format for Blank numeric values

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;

Grand Advisor
Posts: 16,304

Re: How to Apply proc format for Blank numeric values

Proc format;

value custom_fmt

0, . = '-'

other=[percent8.2];

run;

Grand Advisor
Posts: 9,315

Re: How to Apply proc format for Blank numeric values


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;

Contributor
Posts: 27

Re: How to Apply proc format for Blank numeric values

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,

Post a Question
Discussion Stats
  • 6 replies
  • 495 views
  • 0 likes
  • 3 in conversation