left justify on title causing it to lose wrap when output to excel

[Dougall]

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;

[Cynthia_sas]

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]

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 · Accepted solution]

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.