topic tagsets.msoffice2k_x titles? in ODS and Base Reporting

tagsets.msoffice2k_x titles?

ja_lew — Mon, 19 Sep 2011 16:40:21 GMT

ok first of VERY new to tagsets, starting messing with excelxp just a few days ago and have started picking through msoffice2k_x just this morning.

i am having an issue with the titles in msoffice2k_x.

originally i was using excelxp, but need panels so i tried htmlpanel but need multiple sheets so i'm working with msoffice2k_x and am able to format my report the way i want however i cannot get a title to show. i have 8 proc reports on 1 page (see images).

options:

data _null_;

call symputx('_pmenddt',put(intnx('MONTH',today(),-2,'E'),worddate.));

run;

%put &_pmenddt.;

Title1 c=black f=calibri h=3 bold 'Title of Report';

Title2 c=black f=calibri h=2 bold "Section 4: 2010 Ranking";

Title3 c=black f=calibri h=1 bold "Results are through &_PMENDDT.";

ods tagsets.msoffice2k_x file="MSRanking.xls" style=cm_lan

options( fittopage="yes"

sheet_name="2010 Campaign Ranking"

embedded_footnotes="no"

panelcols="4"

panel_space="1"

gridlines="off"

embedded_titles="no"

);

i dont want embedded titles because i only want one main title instead of the same title over each report however it seems it is completely ignoring my title statements.

a picture is worth 1000 words so...

pic 1: current state

pic 2: what is needed

tagsets.msoffice2k_x titles?

Tom — Mon, 19 Sep 2011 20:23:36 GMT

Why not generate the "title" lines in the report using ODS TEXT statements?

I am not sure how that would interact with panels.

tagsets.msoffice2k_x titles?

ja_lew — Mon, 19 Sep 2011 20:33:42 GMT

i thought ods text was only supported by pdf/rtf. anywho i've only a few hours of exposure to ODS. picking through it slowly

code:

ods tagsets.msoffice2k_x file="MSRanking.xls" style=cm_lan

options(

sheet_name="2010 Campaign Ranking"

panelcols="4"

panel_space="1"

gridlines="no"

embedded_titles="no"

);

ods text= 'Title of Report';

ods text= "Section 4: 2010 Ranking";

ods text= "Results are through &_PMENDDT.";

log:

67084 ods text= 'Title of Report';

----

180

67085 ods text= "Section 4: 2010 Ranking";

----

180

67086 ods text= "Results are through &_PMENDDT.";

----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

tagsets.msoffice2k_x titles?

Reeza — Mon, 19 Sep 2011 21:13:29 GMT

Two options I can see, one is use ODS TEXT= to insert titles.

Second is to insert titles, print a blank line and then your output. Trick Is I'm not sure how to 'print' a blank line without a title, though I'm sure you could...proc report is my guess.

The following uses the ods text= option (no errors here).

Neither solution is perfect but I think its closer to what you were looking for.

ods tagsets.msoffice2k_x file="c:\temp\panels.xls"

options(panelcols="1" embedded_titles='yes'

gridlines='no') style=normal;

ods tagsets.msoffice2k_x text= 'This is my main title';

ods tagsets.msoffice2k_x text= 'This is my second title';

ods tagsets.msoffice2k_x text= 'This is my third title';

ods tagsets.msoffice2k_x options(panelcols="3") ;

proc print data=sashelp.prdsale(obs=10);

var actual predict country region;

where country="CANADA";

title "Canada Sales";

run;

proc print data=sashelp.prdsale(obs=10);

var actual predict country region;

where country="U.S.A.";

title "USA Sales";

run;

proc print data=sashelp.prdsale(obs=10);

var actual predict country region;

where country="GERMANY";

title "Germany Sales";

run;

ods tagsets.msoffice2k_x options(panelcols="2") ;

proc print data=sashelp.prdsale(obs=20);

where region="EAST";

title "East Sales";

run;

proc print data=sashelp.prdsale(obs=20);

where region="WEST";

title "West Sales";

run;

ods tagsets.msoffice2k_x close;

If you want to try the second add this in to the beginning instead of the ods text statements:

title 'This is my main title';

title2 'This is my second title';

title3 'This is my third title';

data blank_line;

test='';

run;

proc report data=blank_line nowd;

columns test;

define test/display '';

run;

tagsets.msoffice2k_x titles?

ja_lew — Tue, 20 Sep 2011 14:02:24 GMT

this was very helpful (both replies were). as you can tell, i'm ODS impaired lol, very limited exposure but i do have a question on the ods tagsets.msoffice2k_x text= 'xxxx' lines. is there a way to format the text directly inline (i.e just=l etc) or would i just create a template for those?

also is there a way to turn off panel_spacing for those lines and back on for the reports? each text= puts a blank row underneath of it above the next text= statement.