The SAS Output Delivery System and reporting techniques

tagsets.msoffice2k_x titles?

Reply
Occasional Contributor
Posts: 16

tagsets.msoffice2k_x titles?

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"

);

<snip extra options/reports>

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

msoffice2k_x.PNG

pic 2:  what is needed

msoffice2k_x2.PNG

Super User
Super User
Posts: 6,502

tagsets.msoffice2k_x titles?

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

I am not sure how that would interact with panels.

Occasional Contributor
Posts: 16

tagsets.msoffice2k_x titles?

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.

Super User
Posts: 17,868

tagsets.msoffice2k_x titles?

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;

Occasional Contributor
Posts: 16

tagsets.msoffice2k_x titles?

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.

Ask a Question
Discussion stats
  • 4 replies
  • 323 views
  • 6 likes
  • 3 in conversation