DATA Step, Macro, Functions and more

Numeric Format in Excel ODS

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Numeric Format in Excel ODS

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;


Accepted Solutions
Solution
‎12-05-2015 01:03 PM
Super User
Posts: 19,780

Re: Numeric Format in Excel ODS

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

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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Numeric Format in Excel ODS

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;

Contributor
Posts: 34

Re: Numeric Format in Excel ODS

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.
Solution
‎12-05-2015 01:03 PM
Super User
Posts: 19,780

Re: Numeric Format in Excel ODS

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

format:###,####,##0.00
Contributor
Posts: 34

Re: Numeric Format in Excel ODS

Reeza,
Thank you for the solution! It worked. Smiley Happy
Regards,
Asim
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 515 views
  • 0 likes
  • 3 in conversation