BookmarkSubscribeRSS Feed
mgorripati
Obsidian | Level 7

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. 

7 REPLIES 7
Ksharp
Super User
It is proc report thing. Plz post it at ODS Report forum.
using style=

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

mgorripati
Obsidian | Level 7

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
Super User
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
Super User

Proc format;

value custom_fmt

0, . = '-'

other=[percent8.2];

run;

Ksharp
Super User

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
Obsidian | Level 7

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
Fluorite | Level 6

Hi firend...

 

did you try with this?

 

OPTION MISSING=' '

 

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 7733 views
  • 0 likes
  • 4 in conversation