The SAS Output Delivery System and reporting techniques

Add blank row between tables with style.normal

Reply
Occasional Contributor
Posts: 5

Add blank row between tables with style.normal

[ Edited ]

I can take away the page break from some styles pretty easily (style.nobreak is well documented).  I just can't add one to a style that doesn't have one normally.  If I use a style like SASWeb, it adds the blank row, but I like the Normal appearance better.

 

I'm using SAS EG 9.4, ods excel, two proc tabulates, and this style.  I want to have a blank row between the styles.  Any thoughts?

proc template;
define style wesstyle;
parent = styles.normal;
style rowheader from rowheader /
background = #ffffff
color = #000000
font_weight= bold;
style header from header /
background = #CC0000
color = #ffffff
font_weight= bold
verticalalign =m;
end;
run;

 

Occasional Contributor
Posts: 5

Re: Add blank row between tables with style.normal

bump?

Super User
Posts: 10,529

Re: Add blank row between tables with style.normal

"Bump" does not move the appearance of your post.

 

You would have to show some data, the table generating code and and exactly how you are sending the results to Excel (since there are a largish number of options).

 

I do get a blank line between tables with RTF, or at least space between tables. So it may be the particular way you send your results to Excel that is having an effect on the appearance. Here is the code I used, change the file path to something your system can use. If the appearance with RTF is as expected then you need to seriously examine every option you are using in Excel.

ods rtf file="D:\data\junk.rtf"
style=wesstyle startpage=no;

proc tabulate data=sashelp.class;
   class sex;
   var age;
   tables sex*age,n;
run;

proc tabulate data=sashelp.class;
   class sex;
   var age;
   tables sex*age,n;
run;

ods rtf close;

And this creates XML that can be opened in Excel that has a space between tables

 

ods tagsets.excelxp file="D:\data\junk.xml"
style=wesstyle options(sheet_interval='NONE');

proc tabulate data=sashelp.class;
   class sex;
   var age;
   tables sex*age,n;
run;

proc tabulate data=sashelp.class;
   class sex;
   var age;
   tables sex*age,n;
run;

ods tagsets.excelxp close;

If you provide data please do not use Excel. Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against. 

Occasional Contributor
Posts: 5

Re: Add blank row between tables with style.normal

I am using ODSExcel, not RTF or Tagsets.

 

I mentioned that if I change the style base to SASWeb, that it works fine.

 

The data doesn't matter.

 

Bump did move it to the top.

 

Thanks for the attempt.

SAS Super FREQ
Posts: 8,743

Re: Add blank row between tables with style.normal

[ Edited ]

Hi:
STYLES.NORMAL was originally written specifically for SAS Web Report Studio. It is a style that was meant to be displayed in a browser, NOT in Word, NOT in PDF and NOT in Excel. The appearance or not of extra space between tables is entirely up to the rendering software and STYLES.NORMAL was meant to be rendered by a browser running the Web Report Studio Java-based client. So if you get a blank line between tables in other destinations, that is good, but the truth is that you cannot expect a style to behave the same way in EVERY destination. For example, STYLES.DEFAULT was originally designed with relative numbers for font sizes and STYLES.PRINTER was originally designed with PT numbers for font sizes -- due to the rendering software - -browser vs PDF reader. If you use styles.default with a brower, the font sizes are relative to that browser's default settings. If you use styles.default with ODS PDF -- then you may or may not get the same sizes that you would get in a browser. The final rendering depends on the destination.

If the colors and fonts are what you want for STYLES.NORMAL, but the break between tables is NOT what you want, when you use the style with ODS EXCEL, then I would recommend flipping around your approach and declare the parent of your new style to be STYLES.SASWEB or whatever and then change the HEADER and ROWHEADER styles to have the same attributes as you find in STYLES.NORMAL.

cynthia

Occasional Contributor
Posts: 5

Re: Add blank row between tables with style.normal

[ Edited ]

I'm sorry, this doesn't answer the question either.  There has to be something in the style that is controlling the page breaks.  If you can turn it off, you can turn it on....

SAS Super FREQ
Posts: 8,743

Re: Add blank row between tables with style.normal

[ Edited ]

Hi:
You're right. It does not answer the question. I gave you a workaround because I do not know what causes the extra line between tables in SASWEB and how STYLES.NORMAL differs when used with ODS EXCEL. Tracing inheritance and what is different between 2 style templates is tedious and sometimes not productive if there is a workaround. So I don't know what you would need to "turn on" with STYLES.NORMAL to get the behavior you want.

But from having worked with style templates since the beginning of style templates with ODS, I also understand that some behaviors cannot be "fixed" as simply as you might desire when you try to take a style designed for one destination and use it with another destination.

That is why I included in my explanation that STYLES.NORMAL was designed before ODS EXCEL existed and was NOT designed for a desktop client like Excel. Some styles are designed for only one rendering application. For example, PowerPointLight and PowerPointDark, are designed to work with PowerPoint and thus, I would not expect them to work with Excel or Word as they do in PowerPoint. Nor would I try to change those styles so they would work with Word or Excel.

I spent some time trying to figure out whether there was an obvious style element/attribute that could be easily changed and I could not find one. Hence the workaround.

I know that what I suggested was not a solution. If you want to pursue your original approach to make STYLES.NORMAL behave the same way as STYLES.SASWEB when you use it with ODS EXCEL, then that is a question for Tech Support. Or that is a question for rigorous testing and the ONLY way I know to do the testing is to start with both STYLES.SASWEB and STYLES.NORMAL and try to reverse engineer SASWEB to see what causes the line and what you have to change to get rid of the blank line. Then, when you find that style element/attribute, you can try to insert it into your version of STYLES.NORMAL (and then test to see if it works with NORMAL).

Another reason I suspect that it is NOT the style template, but the either the underlying markup language being written (XLSX) or the way Excel interprets or renders the markup language, is that IF I take the "vanilla" NORMAL style and use it with TAGSETS.EXCELXP versus ODS EXCEL, I get a blank line between tables with TAGSETS.EXCELXP and no blank line between tables using ODS EXCEL.

Another possible workaround is a "blank" title statement before the second tabulate step:
title " ";
and using embedded_titles='yes' suboption.

Sorry that I could not be of more help. Perhaps Tech Support will have more insight.

cynthia

Here's the test code I ran that convinced me that it wasn't the style, by itself that was the root of the problem.

title; footnote;

** ODS MSOFFICE2K and ODS EXCEL are the ONLY 2 destinations that do not render a blank line between the ;
** two tables. All the other destinations insert a blank line between the tables.;
ods msoffice2k(id=1) file='c:\temp\ex_normal_mso.xls' style=normal;
ods tagsets.excelxp(id=2) file='c:\temp\ex_normal_xp.xml' style=normal options(sheet_interval='none');
ods excel(id=3) file='c:\temp\ex_normal_odsex.xlsx' style=normal options(sheet_interval='none' );
ods rtf(id=4) file='c:\temp\ex_normal_rtf.rtf' style=normal startpage=no;
ods pdf(id=5) file='c:\temp\ex_normal_pdf.pdf' style=normal startpage=no;
ods html(id=6) file='c:\temp\ex_normal_ht4.html' style=normal;

proc  tabulate data=sashelp.class;
  class age sex;
  var height;
  table age all,
        height=' '*sex*(min mean max);
run;

proc  tabulate data=sashelp.class;
  class age sex;
  var weight;
  table age all,
        weight=' '*sex*(n css uss);
run;

ods _all_ close;
Ask a Question
Discussion stats
  • 6 replies
  • 229 views
  • 0 likes
  • 3 in conversation