The SAS Output Delivery System and reporting techniques

Controlling the Space between Titles

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Controlling the Space between Titles

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

screen1.JPG

Report should look like

screen2.JPG


Accepted Solutions
Solution
‎08-08-2014 05:49 PM
SAS Super FREQ
Posts: 8,820

Re: Controlling the Space between Titles

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

View solution in original post


All Replies
Solution
‎08-08-2014 05:49 PM
SAS Super FREQ
Posts: 8,820

Re: Controlling the Space between Titles

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

Contributor
Posts: 31

Re: Controlling the Space between Titles

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>

SAS Super FREQ
Posts: 8,820

Re: Controlling the Space between Titles

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;


possible_workaround_title.png
Contributor
Posts: 31

Re: Controlling the Space between Titles

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1548 views
  • 3 likes
  • 2 in conversation