Create a multi color title in proc report ods target.excelxp

[Smijoss]

Hi everyone,

I was able to overcome most hurdles but now stuck at this one ..

I tried to use the  Compute before page  Line @001   command but   the Xml file cannot be opened in excel then.

Is it possible to create a multi colour title ?   Also can we create an excel with transparent background using ODS target.excelxp ?

In EG it shows up as :

However in excel it shows up as -

Code :

Proc report :

title3 justify=left height=9pt font=Arial bold " Data As-of : " color=blue height=9pt font=Arial italic "&Run_Month.";

title4 justify=left height=9pt font=Arial bold " Report ID  : " color=blue height=9pt font=Arial italic "5400";

title5 justify=left height=9pt font=Arial bold " REGION     : " color=blue height=9pt font=Arial italic "&Reg_name.";

title6 justify=left height=9pt font=Arial bold " LOB        : " color=blue height=9pt font=Arial italic "&LOB.";

title7 justify=left height=9pt font=Arial bold " Report Date: " color=blue height=9pt font=Arial italic "%sysfunc(date(),worddate.)";

[ballardw]

You likely have to code this into more xml looking values directly instead of using SAS justify font and color. You can try creating a title in Excel and then save as xml spreadsheet and then find the results in the document. Hint: Don't put lots of data or you may be looking for a long time.

Unless you're doing this often it may be easier to settle for editing in Excel.

This is what I find in the xml when saved from Excel where I made the word "title" blue. the text for the title was "Color this title blue"

   <PageSetup>

    <Header x:Margin="0.3" x:Data="&amp;CColor this &amp;K0070C0title&amp;K01+000 blue"/>

    <Footer x:Margin="0.3"/>

    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>

   </PageSetup>

[Smijoss]

Hi Ballardw,

Thank you for the quick response.

Is there a way I can put this xml code in the sas code  so that sas would create it accordingly ?

[Cynthia_sas · Accepted solution]

Hi:

  I don't think you need to go down the XML road. If you just switch to using ODS ESCAPECHAR, I think you can do it this way, by putting the bold/italic and color in the style override and the  stuff that's all the same in the TITLE attributes.

Cynthia

ods tagsets.excelxp file='c:\temp\tcolor.xml' style=sasweb

    options(embedded_titles='yes');

ods escapechar='^';

Proc report data=sashelp.class(obs=5) nowd;

title1 j=l h=9pt font=Arial  "^{style[fontweight=bold]Data as-of: ^{style[color=blue fontstyle=italic]March 2014}}";

title2 j=l h=9pt font=Arial  "^{style[fontweight=bold]Report ID: ^{style[color=blue fontstyle=italic]5400}}";

title3 j=l h=9pt font=Arial  "^{style[fontweight=bold]REGION: ^{style[color=blue fontstyle=italic]Some Region}}";

title4 j=l h=9pt font=Arial  "^{style[fontweight=bold]LOB: ^{style[color=blue fontstyle=italic]My LOB}}";

title5 j=l h=9pt font=Arial  "^{style[fontweight=bold]Report Date: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate.)}}";

column name age height weight;

run;

ods _all_ close;

[Smijoss]

Thank you Cynthia !!! You are awesum