topic Re: Why only % after ods excel in ODS and Base Reporting

Why only % after ods excel

zhangda — Sat, 15 Oct 2016 09:30:16 GMT

Hello,

I am coding to report into an excel as below, where I want all three columns dayA dayB dayC in percent8.2 format, such as 89.28%, but why for some data, I could only get %, but without 89.28; for some, I only get 90%, which has rounded to percent 8.0 automatically. Please help, thanks a lot!

Proc report data=sum contents=" " nowd split='\'
style(header)={color=black just=center background=lightgrey fontweight=bold }
style(column)={color=black just=center background=lightcyan foreground=black };
title1 j=c color=black HEIGHT=14pt BOLD "band31+";
column strat_band dayA dayB dayC;

define strat_band/ "Band" style=[fontstyle=Roman fontweight=bold width=2in font_size=2.2 cellwidth=2in just=center foreground=black];
define dayA/ "31+Day " ;
define dayB/ "31+A" ;
define dayC/ "31+B" ;

%macro comp(arg);
compute &arg;
if strat_band= '190-194' then call define(_col_,'format','percent8.2');
if strat_band= '225-229' then call define(_col_,'format','percent8.2');

if strat_band= '225-229' then call define(_col_,'format','percent8.2');
if strat_band= '230-234' then call define(_col_,'format','percent8.2');
if strat_band= '235-239' then call define(_col_,'format','percent8.2');
if strat_band= '240-244' then call define(_col_,'format','percent8.2');
if strat_band= '245-249' then call define(_col_,'format','percent8.2');

endcomp;
%mend common_lines;

%comp(dayA );
%comp( dayB );
%comp( dayC);
RUN;

Re: Why only % after ods excel

Kurt_Bremser — Sat, 15 Oct 2016 09:48:45 GMT

You need to supply test data against which the code can be run to illustrate your problem.

Do so in a data step with datalines.

Re: Why only % after ods excel

Reeza — Sat, 15 Oct 2016 09:59:20 GMT

Try percent12.2

And check if Excel is helpfully adjusting your formats. Compare the value that appears to the value in the formula bar. If it is, you'll need to use a tagattr format statement instead of SAS format.

See this thread for examples, he ended up using both tagattr and increasing the width to 12.2

https://communities.sas.com/t5/ODS-and-Base-Reporting/Outputting-Percent-format-in-ODS-EXCEL/td-p/199540/highlight/true/page/2

Re: Why only % after ods excel

ballardw — Mon, 17 Oct 2016 15:18:13 GMT

You may also want to look at the values of STRAT_BAND.

If strat_band has any other value then those you show in the comparison then there is no call define and you'll get a default format.