Hi,
I there are way to control the spacing between the Titles.
For example in the Report below (generated using a stored procedure in excel addin) there s an extra row between Title2 -Title 3 and Title3-Title4.
I wish to remove those space, However i want to keep the Space between Title1 and Title2 and similarly the space between Title3 and Title4.
I was hoping if this could be done within the stored process and not through excel VBA code.
Current report
Report should look like
Hi:
As with your previous question about Footnotes, the Titles that you see are impacted by the STYLE that you specify either in the stored process options inside Excel or Word or by the style that you specify in the override for &_ODSSTYLE in the stored process. Did you try changing to a different style? That was the suggestion that worked for you in this previous track of yours https://communities.sas.com/thread/60078 -- just checking. In addition to style, again, the destination that you've chosen for your stored process -- CSV, HTML or SASReport can have an impact on the title, too.
I am not on a computer where I can test stored process code right now. If you need help immediately, you might consider opening a track with Tech Support on this. And, besides, you didn't post any code. Do your titles behave the same way if your use SASHELP.CLASS in a test program or are you generating your code with a SAS Macro program for the stored process? Again, without seeing code, it's hard to comment and since your question is about a stored process, understanding the stored process settings, overrides, destination and style is also part of answering the question.
cynthia
Hi:
As with your previous question about Footnotes, the Titles that you see are impacted by the STYLE that you specify either in the stored process options inside Excel or Word or by the style that you specify in the override for &_ODSSTYLE in the stored process. Did you try changing to a different style? That was the suggestion that worked for you in this previous track of yours https://communities.sas.com/thread/60078 -- just checking. In addition to style, again, the destination that you've chosen for your stored process -- CSV, HTML or SASReport can have an impact on the title, too.
I am not on a computer where I can test stored process code right now. If you need help immediately, you might consider opening a track with Tech Support on this. And, besides, you didn't post any code. Do your titles behave the same way if your use SASHELP.CLASS in a test program or are you generating your code with a SAS Macro program for the stored process? Again, without seeing code, it's hard to comment and since your question is about a stored process, understanding the stored process settings, overrides, destination and style is also part of answering the question.
cynthia
Hi Cynthia,
I am using HTML output. I ran a script to create result for all style and I see all have the same blank line after each row.
I played around with the HTML file generated to understand what needs to be changed. The only time I could get it right was when the
Class statement for SystemTitle2/3 was changed to <pre>title2 Here <br> title3 Here </pre>.
I guess its easier to code in VBA,
SAS Code :
%let _ODSSTYLE=&STYLES.;
%stpbegin;
options mprint;
%MACRO CLASS_PRINT(SEX);
ODS tagsets.excelxp OPTIONS(row_heights='15,50,0,30,0,0,0') ;
PROC REPORT DATA=SASHELP.CLASS (WHERE=(SEX="&SEX.") OBS=3);
TITLE1 "title1 Here";
TITLE2 "title2 Here";
TITLE3 "title3 Here";
TITLE4 "title4 Here";
RUN;
%MEND;
%CLASS_PRINT(M);
*%CLASS_PRINT(F);
%stpend;
---------------------HTML CODE Generated-------------------------
<html xmlns:v="urn:schemas-microsoft-com:vml">
<head>
<meta name="Generator" content="SAS Software Version 9.2, see www.sas.com">
<meta http-equiv="Content-type" content="text/html; charset=utf-8">
<title>SAS Output</title>
<link rel="stylesheet" type="text/css" href="file:///C:/Program%20Files/SAS92/Add-InForMicrosoftOffice/4.3/Styles/AMODefault.css">
<style type="text/css">
<!--
.l {text-align: left }
.c {text-align: center }
.r {text-align: right }
.d {text-align: right }
.j {text-align: justify }
.t {vertical-align: top }
.m {vertical-align: middle }
.b {vertical-align: bottom }
TD, TH {vertical-align: top }
.stacked_cell{padding: 0 }
-->
</style>
<script language="javascript" type="text/javascript">
<!--
function startup(){
}
function shutdown(){
}
//-->
</script>
<style type="text/css">
table {
mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";
}
</style>
</head>
<body onload="startup()" onunload="shutdown()" class="Body" style=" text-align: center;">
<script language="javascript" type="text/javascript">
<!--
var _info = navigator.userAgent
var _ie = (_info.indexOf("MSIE") > 0
&& _info.indexOf("Win") > 0
&& _info.indexOf("Windows 3.1") < 0);
//-->
</script>
<div class="branch">
<a name="IDX"></a>
<h1 class="SystemTitle" style=" text-align: center;">title1 Here</h1>
<h1 class="SystemTitle2" style=" text-align: center;">title2 Here</h1>
<h1 class="SystemTitle3" style=" text-align: center;">title3 Here</h1>
<h1 class="SystemTitle4" style=" text-align: center;">title4 Here</h1>
<div>
<div align="center">
<table class="Table" style=" text-align: center;" cellspacing="1" cellpadding="7" rules="all" frame="box" summary="Procedure Report: Detailed and/or summarized report">
<colgroup>
<col>
<col>
<col>
<col>
<col>
</colgroup>
<thead>
<tr>
<th class="Header" style=" text-align: center;" scope="col">Name</th>
<th class="Header" style=" text-align: center;" scope="col">Sex</th>
<th class="Header" style=" text-align: center;" scope="col">Age</th>
<th class="Header" style=" text-align: center;" scope="col">Height</th>
<th class="Header" style=" text-align: center;" scope="col">Weight</th>
</tr>
</thead>
<tbody>
<tr>
<td class="Data" style=" text-align: right;">Alfred</td>
<td class="Data" style=" text-align: right;">M</td>
<td class="Data" style=" text-align: right;"> 14</td>
<td class="Data" style=" text-align: right;"> 69</td>
<td class="Data" style=" text-align: right;"> 112.5</td>
</tr>
<tr>
<td class="Data" style=" text-align: right;">Henry</td>
<td class="Data" style=" text-align: right;">M</td>
<td class="Data" style=" text-align: right;"> 14</td>
<td class="Data" style=" text-align: right;"> 63.5</td>
<td class="Data" style=" text-align: right;"> 102.5</td>
</tr>
<tr>
<td class="Data" style=" text-align: right;">James</td>
<td class="Data" style=" text-align: right;">M</td>
<td class="Data" style=" text-align: right;"> 12</td>
<td class="Data" style=" text-align: right;"> 57.3</td>
<td class="Data" style=" text-align: right;"> 83</td>
</tr>
</tbody>
</table>
</div>
</div>
<br>
</div>
</body>
</html>
Hi:
I think you should report this to Tech Support. If all title statements are getting the same <H1> tag when written as HTML, then I do not understand exactly why the extra space. Unless that is the Microsoft spec for how to deal with the <H1> tag when the HTML file is rendered to Excel format. But if you get the same results with every style, then that is something that may need to be investigated.
One other thing you could try in SAS is using ODS ESCAPECHAR. As I explained, I'm not in a position to try a stored process right now, but this code got rid of the extra blank lines in Base SAS on Windows with SAS 9.4. It might be worth a try.
Cynthia
ods msoffice2k file='c:\temp\trytitle_mso.xls' style=sasweb;
title1 'T1 here all title statements separate';
title2 'T2 here';
title3 'T3 here';
title4 'T4 here';
title5 'T5 here';
proc print data=sashelp.class(obs=3);
run;
title;
ods escapechar='~';
title1 'T1 here one long string~{newline 1}T2 here~{newline 1}T3 here~{newline 1}T4 here~{newline 1}T5 here';
proc print data=sashelp.class(obs=3);
run;
ods _all_ close;
Thanks Cynthia.
I realized since my output is into excel its lot more easier to get rid of those rows using VBA code than SAS. But your code is good too.
I will remember this for places where i cannot use VBA
Thanks Again
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.