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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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