The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

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

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 :

tmp.JPG

However in excel it shows up as -

tmp2.JPG

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.)";


Accepted Solutions
Solution
‎03-28-2014 08:55 PM
SAS Super FREQ
Posts: 8,645

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

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;

View solution in original post


All Replies
Grand Advisor
Posts: 9,748

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

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" xSmiley Very Happyata="&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>

Contributor
Posts: 31

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

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 ?

Solution
‎03-28-2014 08:55 PM
SAS Super FREQ
Posts: 8,645

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

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;

Contributor
Posts: 31

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

Thank you Cynthia !!! You are awesum Smiley Happy

Post a Question
Discussion Stats
  • 4 replies
  • 581 views
  • 0 likes
  • 3 in conversation