topic Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML. in ODS and Base Reporting

How to remove the blank rows from EXCEL report created from SAS ODS HTML.

Rajeeva — Fri, 31 Mar 2017 13:18:33 GMT

This is my first time using this service. I am creating the EXCEL report using SAS ODS HTML. Somehow it is automatically inserting the blank row after every record in the EXCEL report. I am using SAS 9.4

proc template;
     define style styles.demo05b;
            parent=styles.printer;
            class table /
            frame=void
            just=l
            borderspacing=0
            rules = cols;
     end;
run;

ods html file="/cdoracle/Y2015/reports/TABLE_R37_HTML.xls" style=demo05b;

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

collinelliot — Fri, 31 Mar 2017 14:37:46 GMT

I think your results are due to the "asis = on" option in your define for the "hold_descr" column. I don't have your data, so I cannot confirm this, but try removing it. Whether that breaks some other desired result is another question.

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

Rajeeva — Fri, 31 Mar 2017 14:50:43 GMT

It worked and I really appreciate your help. But now I am confused. Previously I used asis=on option to keep the indentation blanks.

I have two more questions, you already have my program.

1. In the report, my title2 is not left justified. Somehow it is starting from column B.

2. It's not letting me use titlte1 to start from row 1.

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

collinelliot — Fri, 31 Mar 2017 14:59:51 GMT

So my initial solution was based on "what the heck is that 'asis...'?" and just trial and error using fake data (since I don't have yours). But I did find the note (http://support.sas.com/kb/49/853.html) and it does fix the problem without removing the "asis" option. See my example below and maybe that will fix the issue without adverse effects?

ods html body=".....\report_v1.xls"; 
                                                    
proc report data=sashelp.class nowd;
   define name / style(column)=[asis=on] display;
run;

ods html close;


ods html body=".....\report_v2.xls"
    headtext="<style> pre { margin:0 }</style>"; 
                                                    
proc report data=sashelp.class nowd;
   define name / style(column)=[asis=on] display;
run;

ods html close;

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

Rajeeva — Fri, 31 Mar 2017 15:28:40 GMT

I added your headtext option in my program and also kept asis=on option and it worked perfect. YOU ARE GOOD.

Did you see my other 2 questions about title1 and title2.

Your help is really appreciated.

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

collinelliot — Fri, 31 Mar 2017 15:40:43 GMT

Sorry, I missed the question about the titles.

The only thing can see there in your code is that it looks like the opening and closing quotation marks are different: " vs ”

Opening quote different from end quote:

title1 j=left color=white "Table with row headers in column A and column headers in rows 2 through 3”;
title2 &mTOpn1 "&mpart2 &mpart3 &myear ";

That creates imbalanced quotation marks. When I fix that and use those titles, I don't have the issue you describe. Title1 is in A1 and title2 is in A2. Check to see if your issue is the imbalanced quotes, maybe.

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

Rajeeva — Fri, 31 Mar 2017 16:02:28 GMT

Somehow both title1 and title2 are getting wrapped up in column A in row1 and row2.

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

collinelliot — Fri, 31 Mar 2017 16:06:29 GMT

Again, I'd check your quotation marks. They looked imbalanced because whatever the underlying ascii code for the opening quote looks different from the closing quote.

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

Rajeeva — Fri, 31 Mar 2017 16:18:06 GMT

I modified the quotation marks like you suggested. Both titles are still getting wrapped up in column A.

%MACRO print_report ;

          %LET mTOpn1 = bold h=12pt font='Arial' justify=l ;
          %LET mpart2    = TABLE R37. Number of nonfatal occupational injuries and illnesses involving;
          %LET mpart3    = days away from work^{super 1} by industry and age of worker, private industry,;

          title1 &mTOpn1 "Table with row headers in column A and column headers in rows 2 through 3";
          title2 &mTOpn1 "&mpart2 &mpart3 &myear ";

           proc report data=selected_records3 nowd split = '~' STYLE(report)={rules=none }
                                                               STYLE(header)={font_size=10pt font_face='Arial' background=#D3D3D3 }
                                                               STYLE(Column)={tagattr='Format:@'};

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

collinelliot — Fri, 31 Mar 2017 17:06:20 GMT

I think you've exhausted what I can do to easily help you, unfortunately. I tried the below as an alternative, but I still don't think it does what you want.

I would post another question addressing with the specific issue of how to get titles to not wrap using ODS html. There's a SAS employee named Cynthia who knows ODS inside and out, so maybe she'll check it out. I'm not sure if you can put a tag on there that might get her attention.

title;
ods html body="...\report_v2.xls"
    headtext="<style> pre { margin:0 }</style>" style = styles.demo05b; ; 

ods text = "Table with row headers in column A and column headers in rows 2 through 3";
ods text = "&mpart2 &mpart3 &myear ";
                                                
proc report data=sashelp.class nowd;
   define name / style(column)=[asis=on] display;
run;

ods html close;

Re: How to remove the blank rows from EXCEL report created from SAS ODS HTML.

Cynthia_sas — Fri, 31 Mar 2017 17:26:41 GMT

Hi: unfortunately, I am teaching today and unable to run code.

Excel opening HTML is a tricky business. Just because you name the file as .XLS, it is not a true, binary XLS file. Excel is opening an HTML file and doing with it what it wants to do. If you were still running SAS 8, you would be using HTML3 with ODS HTML. If you are using SAS 9, then the default HTML created is HTML 4.0. How your Excel renders that HTML depends on Excel.

One thing you could try is to switch to "microsoft friendly" html and use
ODS MSOFFICE2K file="...";
...code...
ODS MSOFFICE2K close;

That may make Excel happier. It may not. A fall back position would be to work with Tech Support. I can try to take a look when I'm not teaching, but Tech Support might be faster.

cynthia