The SAS Output Delivery System and reporting techniques

Excel exporting with 2 decimals for all values

Reply
Frequent Contributor
Posts: 97

Excel exporting with 2 decimals for all values

I am using the following code to export data into excel.
The excel also has formulas added which I have ignored in this example.

I need some help in outputting the values consistently with 2 decimals for numeric columns and values.
ods listing close;

ods tagsets.excelxp file="&rep_filename."


ods tagsets.excelxp file="&rep_filename."
style=styles.printer
options(embedded_titles='yes' sheet_interval='none'
width_fudge='0.75' sheet_name="&customer_number"
default_column_width="10,0,8,8,8,8,8,8,8" FitToPage = 'yes' PAGES_FITWIDTH = '1' Pages_FitHeight = '100')
;
ods tagsets.excelxp options(embedded_titles='yes'
sheet_interval='none' sheet_name="&customer_number" FitToPage = 'yes' PAGES_FITWIDTH = '1' Pages_FitHeight = '100');

proc report data=mps18_final;
columns cntrows trans_eff_date code description account_number
trans_amount
col_bc
arrears_bal_for;

define cntrows / computed noprint;
define trans_eff_date / display "Transaction Date" center style(column)={outputwidth=1.2in just=center};
define code / display "Code" center format=$10. style(column)={outputwidth=0.70in just=center};
define description / display "Description" format=$100. style(column)={outputwidth=2in};
define account_number / display "Sub Account Number" center format=10. style(column)={outputwidth=1in just=center};
define trans_amount / display "Transaction Amount" format=12.2 style(column)={outputwidth=1in };
define col_bc / display "Account Balance" format=12.2 style(column)={outputwidth=0.70in};
define arrears_bal_for / computed "Arrears Balance" format=12.2 style(column)={outputwidth=0.70in};
run;

I have just provided a sample of the code and not the entire code
Frequent Contributor
Posts: 97

Re: Excel exporting with 2 decimals for all values

Found it...!!!!

define trans_amount / display "Transaction Amount" format=12.2 style(column)={tagattr="format:##0.00" outputwidth=1in };

or

call define(_col_,'style','style={tagattr="formula:R[-1]C+RC[-2] format:##0.00"}');
Ask a Question
Discussion stats
  • 1 reply
  • 244 views
  • 0 likes
  • 1 in conversation