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.
It is proc report thing. Plz post it at ODS Report forum. using style= options missing='-'; ................ proc report .......... column xx/style={just=center}; ...............
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,
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;
Proc format;
value custom_fmt
0, . = '-'
other=[percent8.2];
run;
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;
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,
Hi firend...
did you try with this?
OPTION MISSING=' '
It will be set in the beginning of your program.
it works to me.
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.
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.