The SAS Output Delivery System and reporting techniques

ODS Markup to Excel (format text)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 83
Accepted Solution

ODS Markup to Excel (format text)

[ Edited ]

I have the following code.  I am trying to format ot_hrs to one decimal point.  You will notice mso-number-format:'0\.0' in the code.  When I do this it only formats the section that is highlighted in blue (paleturqoise) and the grand total number to one decimal.  I can't get it to format all OT hours.  Any help with this is appreciated!!

proc report data=work.QUERY_FOR_PAY_TIMEBOOK_MSTR_0003 nowd
style(summary)=[/*color=cx3e3d73*/ backgroundcolor=pink
                /*fontfamily=helvetica fontsize=3*/ textalign=r];

column desc pos last_nm first_nm ot_hrs ccpos;

define desc / order ;

define pos / order;

define last_nm / order ;

define first_nm / order;

define ot_hrs / analysis sum 'OT' style(column)={htmlstyle="mso-number-format:'0\.0'"}  
;

define ccpos /order ;

compute ccpos  ;

IF ccpos  in('331025') THEN CALL DEFINE(_ROW_, "style","STYLE=[BACKGROUND=Paleturquoise]") ;
endcomp;
define ccpos /noprint;

/*cxF7EDFD     pretty pink*/

break after desc /  SUMMARIZE STYLE=[HTMLSTYLE="border-bottom:5px double red;border-left:none;borderright:
none;BORDER-TOP:5px double red"];


rbreak after / summarize skip ;
compute after;
      desc='Grand Total:';
   endcomp;

run;


ODS MARKUP CLOSE;

*  Begin EG generated code (do not edit this line);
;*';*";*/;quit;

*  End EG generated code (do not edit this line);

Accepted Solutions
Solution
‎03-16-2016 10:56 AM
SAS Super FREQ
Posts: 8,743

Re: ODS Markup to Excel (format text)

Hi:

ODS MARKUP could be HTML-based (in which case your HTMLSTYLE specification might be needed) or it could be XML-based (in which case you might need to use TAGATTR). In the future, showing ALL your code, including the ODS statements would be useful. Please consider these 2 statement examples that worked for me:

For HTML-based "markup" destinations, you need to use HTMLSTYLE:

   define ot_hrs / analysis sum 'OT'

             style(column)={htmlstyle="mso-number-format:#0\.0​"};  

 

For XML-based "markup" destinations, you need to use TAGATTR:

   define ot_hrs / analysis sum 'OT'

             style(column)={tagattr="format:#0.0​"};  

 

With either form of usage, notice the correct quoting. You have an extra set of quotes in your specification, which is incorrect, but also, without seeing your ODS statements, it is hard to determine whether you need HTMLSTYLE or TAGATTR. The code shown in the screen shot worked correctly for me in all 3 outputs.

 

cynthia

 

compare_change_format.png

 

View solution in original post


All Replies
Super User
Posts: 17,819

Re: ODS Markup to Excel (format text)

Are you using ODS TAGSETS.EXCELXP or ODS EXCEL? 

Or another variant?

Frequent Contributor
Posts: 83

Re: ODS Markup to Excel (format text)

ODS Markup
Solution
‎03-16-2016 10:56 AM
SAS Super FREQ
Posts: 8,743

Re: ODS Markup to Excel (format text)

Hi:

ODS MARKUP could be HTML-based (in which case your HTMLSTYLE specification might be needed) or it could be XML-based (in which case you might need to use TAGATTR). In the future, showing ALL your code, including the ODS statements would be useful. Please consider these 2 statement examples that worked for me:

For HTML-based "markup" destinations, you need to use HTMLSTYLE:

   define ot_hrs / analysis sum 'OT'

             style(column)={htmlstyle="mso-number-format:#0\.0​"};  

 

For XML-based "markup" destinations, you need to use TAGATTR:

   define ot_hrs / analysis sum 'OT'

             style(column)={tagattr="format:#0.0​"};  

 

With either form of usage, notice the correct quoting. You have an extra set of quotes in your specification, which is incorrect, but also, without seeing your ODS statements, it is hard to determine whether you need HTMLSTYLE or TAGATTR. The code shown in the screen shot worked correctly for me in all 3 outputs.

 

cynthia

 

compare_change_format.png

 

Frequent Contributor
Posts: 83

Re: ODS Markup to Excel (format text)

Thank you Cynthia

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 553 views
  • 2 likes
  • 3 in conversation