BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dougall
Fluorite | Level 6

Hi,

 

I am having an issue using ODS tagsets.excelxp, where my title and footnotes, which are supposed to wrap, are not doing so when left justified. Mysteriously, they wrap when the justify is set to either center or right. I have some example code here, which is a basic version of what I am working on. Any help would be appreciated!

 

P.S. Using SAS 9.41M3, windows 64-bit OS

 

/* start of example code */

 

%let outpath = ;
%let file_name = outtest;

 
/* make some fake data */
data data1;
length var1 3. var2 3. var3 3. value 8. value_cv 8.;
var1 = .; var2 = .; var3 = .; value = 10; value_cv=1; output; var1 = 1; var2 = .; var3 = .; value = 1; value_cv=1; output; var1 = 1; var2 = 1; var3 = .; value = 1; value_cv=1; output; var1 = 1; var2 = 2; var3 = .; value = 1; value_cv=1; output; var1 = 2; var2 = .; var3 = .; value = 1; value_cv=1; output; var1 = 2; var2 = 1; var3 = .; value = 1; value_cv=1; output; var1 = 2; var2 = 2; var3 = .; value = 1; value_cv=1; output; var1 = .; var2 = .; var3 = 1; value = 1; value_cv=1; output; var1 = .; var2 = 1; var3 = 1; value = 1; value_cv=1; output; var1 = .; var2 = 2; var3 = 1; value = 1; value_cv=1; output; var1 = .; var2 = .; var3 = 2; value = 1; value_cv=1; output; var1 = .; var2 = 1; var3 = 2; value = 1; value_cv=1; output; var1 = .; var2 = 2; var3 = 2; value = 1; value_cv=1; output; run; /* start of ods */ ods listing close; ODS tagsets.excelxp file="&outpath\&file_name..xml" style=SASWEB; ODS escapechar = "^"; ODS tagsets.excelxp options( FitToPage='yes' Merge_Titles_Footnotes='yes' Embedded_Titles='yes' Embedded_Footnotes='yes' WrapText="yes" Print_Header_margin="0.1" Print_Footer_margin="0.1" ); ODS tagsets.excelxp options ( Orientation='Landscape' SHEET_NAME="Sheet1" SHEET_INTERVAL="None" Absolute_Column_Width="10,10,10,1.57,10,1.57,10,1.57" Autofit_height="yes" wraptext='yes' ); proc report nowd data=data1 spanrows missing style(header)=[BACKGROUNDCOLOR=GRAYE6 foreground=black fontweight=BOLD VJUST=M]; column ("VAR1" var1) ("VAR2" var2) ("VALUES"("VAR3" var3),(value value_cv)); define var1 / group '' order=data Style=[JUST=L VJUST=M]; define var2 / group '' order=data Style=[JUST=L VJUST=M]; define var3 / across '' order=data Style=[JUST=C VJUST=M]; define value / '' style=[tagattr='format:#,###,##0' VJUST=M FONTSIZE=10pt]; define value_cv / '' Style=[just=L VJUST=M FONTSIZE=8pt]; compute var1; if var1 = . then do; call define ("var1",'style',"style=[fontweight=bold TEXTINDENT=0]"); end; if var1 ne . then do; call define ("var1",'style',"style=[fontweight=light TEXTINDENT=1]"); end; endcomp; compute var2; if var2=. then do; call define ("var2",'style',"style=[fontweight=BOLD BACKGROUNDCOLOR=GRAYE6]"); end; if var2 ne . then do; call define ("var2",'style',"style=[fontweight=light TEXTINDENT=1]"); end; endcomp; compute value; if var2=. then do; call define (_col_,'style',"style=[fontweight=BOLD BACKGROUNDCOLOR=GRAYE6]"); end; endcomp;
compute value_cv; if var2=. then do; call define (_col_,'style',"style=[fontweight=BOLD BACKGROUNDCOLOR=GRAYE6]"); end; endcomp; TITLE; Title1 font="Arial" COLOR=BLACK justify=left /* error occurs with this justify */ "^{style [fontweight=BOLD]<Font html:Size='16'>TITLE1</Font>} ^n ^{style [fontweight=BOLD]<Font html:Size='16'>TITLE2</Font>} ^n ^{style [fontweight=BOLD]<Font html:Size='13'>TITLE3</Font>} ^n ^{style [fontweight=BOLD]<Font html:Size='13'>TITLE4</Font>} ^n ^{style [fontweight=LIGHT]<Font html:Size='8'>TITLE5</Font>}"; FOOTNOTE; Footnote1 wrap font="Arial" COLOR=BLACK justify=left /* error occurs with this justify */ "^{style [fontweight=BOLD]<Font html:Size='10'>FNOTE1</Font>} ^nFNOTE2 ^nFNOTE3 ^nFNOTE4 ^nFNOTE5 ^nFNOTE6 ^n FNOTE7 ^n FNOTE8 ^n FNOTE9 ^n FNOTE10 ^n FNOTE11"; run; TITLE; FOOTNOTE; ODS tagsets.excelxp close; ods listing;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Dougall
Fluorite | Level 6

Since this workaround was done because of having >10 footnotes per sheet, I solved this by outputting the footnotes as a dataset beneath the actual dataset, and then hard-coding the title formatting in Title statements (since only the words need to change, not the formatting). Just an fyi if anyone reads this topic.

View solution in original post

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

Hi,

you may want to correct your code by putting it into a SAS code window (the little icon of a notepad with a running man on it).

 

  I can't read your code and don't understand why you appear to be sending HTML tags in the title to a destination like TAGSETS.EXCELXP that is NOT HTML-based.

 

  I'm not sure I even expect your <FONT> tag to work. But I don't see that you have a long enough title to wrap and also, ODS TAGSETS.EXCELXP doesn't use old style "line break" as I remember (like ^n) -- it sticks in my mind that you have to use the equivalent of Alt+Enter for TAGSETS.EXCELXP.... so I'm wondering why you are using ^n along with the <FONT> tag.

Cynthia

Dougall
Fluorite | Level 6

Hi Cynthia,

 

Just edited to insert the code.

 

This is the only way we were able to make the report generate how we needed it to look. Adding the title statement in that way created a 5-line title, in one cell, with no extra spacing around it.

 

If I run this, but switch the Title1 or Footnote1 justify=left option to center or right, the title is displayed in the proper way. Again, this is mock data, my actual title is not title1 title2 etc. ^n has been working for us, and continues to work properly, except with the justify=left.

 

 

Thanks,

 

Dougall

Dougall
Fluorite | Level 6

Since this workaround was done because of having >10 footnotes per sheet, I solved this by outputting the footnotes as a dataset beneath the actual dataset, and then hard-coding the title formatting in Title statements (since only the words need to change, not the formatting). Just an fyi if anyone reads this topic.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2072 views
  • 0 likes
  • 2 in conversation