The SAS Output Delivery System and reporting techniques

PROC REPORT Define format and style

Reply
Frequent Contributor
Frequent Contributor
Posts: 119

PROC REPORT Define format and style

ods output,

 

PROC REPORT ...

define ConfigRework_Percent/computed format=percent10.2 style=[tagattr="format:##00.##00%"];

compute ConfigRework_Percent;

ConfigRework_Percent=_c19_/_c12_;

endcomp;

 

From SAS Proc Report output data:

 

I have data as: it should be the same value and format on excel file.

                        0.002%

                        1.60%

                        0.61%

 

but ods Excel file the data change to:

                      02.17696%

                      159.6430%

                       60.9298%

 

in order maintain the same value and format, what I should change ?

   style=[tagattr="format:##00.##00%"];

               

SAS Super FREQ
Posts: 9,254

Re: PROC REPORT Define format and style

Hi:
  You do not show all of your code. For example, TAGATTR can be used with ODS TAGSETS.EXCELXP and ODS EXCEL. Since you do not show the ODS statements or even provide any data, it is hard to make any constructive suggestions. However, without looking at your code, I can clarify how the TAGATTR works for percents. As you show your TAGATTR format value, you are specifying the form of TAGATTR that instructs Excel to multiply the value by 100 before applying the format to the number. If you do NOT want to multiply your number by 100, then you need to insert a \ (backslash) into the format, before the percent sign.

  Here's an example with some fake data and using 2 different TAGATTR specifications.

percent_format.png

 

Here's the code for ODS  TAGSETS.EXCELXP and ODS EXCEL.

data testpct;
  infile datalines dlm=',';
  input idvar pct1 pct2;
  origval = pct1;
return;
datalines;
1,0.21, 0.21
2,10.3, 10.3
3,99.9, 99.9
;
run;
 
ods excel file='c:\temp\testpct.xlsx';
ods tagsets.excelxp file='c:\temp\testpct_xp.xml' style=htmlblue;
 
proc report data=testpct;
column idvar origval pct1 pct2;
define idvar / order;
define pct1 / 'Example 1 will multiply by 100' 
       style(column)={tagattr='format:##0.00%'};
define pct2 / 'Example 2 with \ will NOT multiply by 100' 
       style(column)={tagattr='format:##0.00\%'};
run;
ods _all_ close;

  So you have to decide whether you want Excel to multiply by 100 or not and specify your TAGATTR accordingly. Also, note the difference between my STYLE override and yours. It is a best practice with PROC REPORT to put COLUMN or HEADER after the STYLE in order to be explicit about which area of the report you want to change. After all, you would not apply the TAGATTR to the HEADER cell, only to the DATA cells -- which is what COLUMN specifies as the location.

cynthia

Frequent Contributor
Frequent Contributor
Posts: 119

Re: PROC REPORT Define format and style

Posted in reply to Cynthia_sas
Thank you!

This is working.


Ask a Question
Discussion stats
  • 2 replies
  • 233 views
  • 0 likes
  • 2 in conversation