topic Re: Numeric Format in Excel ODS in SAS Programming

Numeric Format in Excel ODS

asimraja — Fri, 04 Dec 2015 23:30:06 GMT

Hi,

My code creates an XML based Excel file using ODS. I'd like to format the numeric fields using the format "COMMAw.d". For example,

25431.23 would appear as 25,431.23, if I use the format comma10.2.

I tried various combinations (latest attempt in red font below) but can't seem to figure out. Can you please guide me?

Thank you in advance!

Asim

----------------------------------------------------------------------------------

ods tagsets.excelxp file="&base_dir_path./Output/&booking_month./MapBook/&exportname..xls" style=normal;

    ods tagsets.excelxp options(sheet_name="Error-Higher Balance");
   PROC PRINT DATA=higher_balance noobs;
       var SECU_CUSIP_ID / style={TAGATTR='format:text'};
       var issuer_sector;
       var IPIECE_CURR_UPB_AMT upb_old change_in_balance / style = {TAGATTR='format#,##0:numeric'};
   RUN;

ods tagsets.excelxp close;

Re: Numeric Format in Excel ODS

ballardw — Fri, 04 Dec 2015 23:42:29 GMT

I'm not seeing any place you are using the commaw.d format.

Did you try?

format IPIECE_CURR_UPB_AMT upb_old change_in_balance comma10.2;

Re: Numeric Format in Excel ODS

Reeza — Sat, 05 Dec 2015 02:07:09 GMT

For XML you need to use Excel format, not SAS formats. Try the followinf without numeric.

format:###,####,##0.00

Re: Numeric Format in Excel ODS

asimraja — Sat, 05 Dec 2015 18:05:05 GMT

Ballardw,
My apologies that I didn't explain my dilemma clearly. I didn't want to use comma w.d format, as I knew that I had to use an Excel format. I referred to comma w.d format to explain that I want the end result the same as if I'd use w.d format.

Re: Numeric Format in Excel ODS

asimraja — Sat, 05 Dec 2015 18:05:39 GMT

Reeza,
Thank you for the solution! It worked. 🙂
Regards,
Asim