topic Re: How to Apply proc format for Blank numeric values in ODS and Base Reporting

How to Apply proc format for Blank numeric values

mgorripati — Mon, 29 Aug 2016 01:30:38 GMT

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.

Re: How to Apply proc format for Blank numeric values

Ksharp — Mon, 29 Aug 2016 02:44:11 GMT

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

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

Re: How to Apply proc format for Blank numeric values

mgorripati — Mon, 29 Aug 2016 03:42:25 GMT

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.

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:

options missing='-';

DEFINE Script_Count / ANALYSIS FORMAT=comma. CENTER 'Script Counts' style ={tagattr="format:###,###,###"};
EFINE Member_Percent / Analysis CENTER 'Members (%)' style(column)={just=center} ;

RUN:

Thanks,

Re: How to Apply proc format for Blank numeric values

Reeza — Mon, 29 Aug 2016 06:07:17 GMT

Proc format;

value custom_fmt

0, . = '-'

other=[percent8.2];

run;

Re: How to Apply proc format for Blank numeric values

Ksharp — Mon, 29 Aug 2016 09:06:23 GMT

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;

Re: How to Apply proc format for Blank numeric values

Ksharp — Mon, 29 Aug 2016 09:16:14 GMT


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;

Re: How to Apply proc format for Blank numeric values

mgorripati — Mon, 29 Aug 2016 17:15:43 GMT

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,

Re: How to Apply proc format for Blank numeric values

jack_CR — Wed, 14 Jun 2017 19:05:46 GMT

Hi firend...

did you try with this?

OPTION MISSING=' '

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