topic Re: Create a multi color title in proc report ods target.excelxp in ODS and Base Reporting

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

Smijoss — Fri, 28 Mar 2014 18:05:34 GMT

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

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

ballardw — Fri, 28 Mar 2014 21:30:03 GMT

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>

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

Smijoss — Sat, 29 Mar 2014 00:31:39 GMT

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 ?

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

Cynthia_sas — Sat, 29 Mar 2014 00:55:13 GMT

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;

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

Smijoss — Tue, 01 Apr 2014 16:46:37 GMT

Thank you Cynthia !!! You are awesum