topic Re: proc export Excel issue with decimals in SAS Programming

proc export Excel issue with decimals

SASAna — Fri, 20 May 2016 15:31:30 GMT

proc export data=output_xlsd
dbms=xlsx
outfile="&Outdir./XYZ.xlsx"
replace;
run;

XYZ.xlsx is not retaining decimal points while exporting (Ex : 0.00 is showing as 0 , 149.00 is showing as 149)

I tried to add format in the datastep before exporting. It is not retaining from the dataset . IS there any other way to retain the decimals?

Thanks

Re: proc export Excel issue with decimals

Haris — Fri, 20 May 2016 18:55:36 GMT

Try ODS EXCEL or the older ODS TAGSETS.EXCELXP rather than PROC EXPORT? ODS gives you a lot of flexibility with respect to formatting your output.

Re: proc export Excel issue with decimals

nketata — Fri, 20 May 2016 19:25:08 GMT

Few years ago we could write DDE commands to format an Excel sheet from SAS

data _null_;

file DDEcmds;

put '[font.properties("Verdana","Bold",16,,,,,,,3)]';

run;

It no longer works now.

Re: proc export Excel issue with decimals

Ksharp — Sat, 21 May 2016 06:43:18 GMT

If you have to use PROC EXPORT, you could make a new CHARACTER variable to hold numeric number via put(var,f8.2).

Or you could try ODS EXCEL or ODS TAGSETS.EXCELXP .



ods excel file='/folders/myfolders/x.xlsx';
proc report data=sashelp.class nowd;
define weight/style={tagattr="format:####.00"};
run;
ods excel close;