BookmarkSubscribeRSS Feed
ja_lew
Fluorite | Level 6

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

4 REPLIES 4
Tom
Super User Tom
Super User

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

I am not sure how that would interact with panels.

ja_lew
Fluorite | Level 6

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.

Reeza
Super User

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;

ja_lew
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 977 views
  • 6 likes
  • 3 in conversation