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
pic 2: what is needed
Why not generate the "title" lines in the report using ODS TEXT statements?
I am not sure how that would interact with panels.
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.
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;
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.
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.
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.