topic Re: Controlling the Space between Titles in ODS and Base Reporting

Controlling the Space between Titles

Smijoss — Fri, 08 Aug 2014 18:09:14 GMT

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

Re: Controlling the Space between Titles

Cynthia_sas — Fri, 08 Aug 2014 21:49:45 GMT

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

Re: Controlling the Space between Titles

Smijoss — Fri, 08 Aug 2014 23:17:56 GMT

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-------------------------

<head>

<title>SAS Output</title>

<!--

.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>

<!--

function startup(){

}

function shutdown(){

}

//-->

</script>

table {

mso-displayed-decimal-separator:"\.";

mso-displayed-thousand-separator:"\,";

}

</style>

</head>

<!--

var _info = navigator.userAgent

var _ie = (_info.indexOf("MSIE") > 0

&& _info.indexOf("Win") > 0

&& _info.indexOf("Windows 3.1") < 0);

//-->

</script>

<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>

<col>

</colgroup>

<thead>

<tr>

<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>

</tr>

<tr>

<td class="Data" style=" text-align: right;">Henry</td>

</tr>

<tr>

<td class="Data" style=" text-align: right;">James</td>

</tr>

</tbody>

</table>

</div>

<br>

</div>

</body>

</html>

Re: Controlling the Space between Titles

Cynthia_sas — Sat, 09 Aug 2014 03:17:23 GMT

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;

Re: Controlling the Space between Titles

Smijoss — Fri, 29 Aug 2014 18:50:38 GMT

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