BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Phil0917
Fluorite | Level 6

Hi Everyone,

  I have recently been running into a situation where every time I use ODS HTML to create an Excel file with PROC REPORT, the resulting output creates 2 rows in the Excel file for each row of data that is to be written out.  The first row contains all of the expected data, and the second row is completely blank.  However, if I filter the Excel file for "blanks", it will show me all of those row numbers, but with data apparently in them.  If I delete these "blank" rows, then I get the desired single rows with all of the expected data.  Does anyone know how to correct this, so that only 1 row will be created?

BTW - I am using version 9.1.3 at the moment.

Here is an example of the code I used:

ods listing close;

ods html file="\............\Listings\Report\Site Enrollment\Site Enrollment Report_&sysdate9..xls" ;

proc report data=all nowd split="|" nowd style(report)={foreground=black};

   columns siteno site_name pi_full_name icdt actdt count eflag numdays;

   define siteno / display "Site"                                        style(header)=[just=c font_size=11pt font_weight=bold background=lightblue]

                                                                                    style(column)={just=c cellwidth=50 asis=on vjust=c htmlstyle="mso-number-format:'000'"};

   define site_name / display "Site Name"                        style(header)=[just=c font_size=11pt font_weight=bold background=lightblue]

                                                                                    style(column)={just=c cellwidth=500 asis=on vjust=c};

   define pi_full_name / display "PI Name"                        style(header)=[just=c font_size=11pt font_weight=bold background=lightblue]

                                                                                     style(column)={just=c cellwidth=220 asis=on vjust=c};

   define icdt / display "Date First Patient Enrolled at Site"  style(header)=[just=c font_size=11pt font_weight=bold background=lightblue]

                                                                                     style(column)={just=c cellwidth=118 asis=on vjust=c};

   define actdt / display "Site Activation Date"                    style(header)=[just=c font_size=11pt font_weight=bold background=lightblue]

                                                                                      style(column)={just=c cellwidth=100 asis=on vjust=c};

   define count / display "# Patients Enrolled to Date"          style(header)=[just=c font_size=11pt font_weight=bold background=lightblue]

                                                                                      style(column)={just=c cellwidth=80 asis=on vjust=c};

   define eflag / display "More Than 4 Weeks Active With No Enrollment?"  style(header)=[just=c font_size=11pt font_weight=bold background=lightblue]

                                                                                                              style(column)={just=c cellwidth=140 asis=on vjust=c};

   define numdays / display "Number of Days With No Enrollment"              style(header)=[just=c font_size=11pt font_weight=bold background=lightblue]

                                                                                                              style(column)={just=c cellwidth=100 asis=on vjust=c};

run;

ods html close;

ods listing;

Thanks!

-Phil

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  It really comes down to what's in WORK.ALL. When you use ODS HTML, you are not, technically, creating an Excel file. You are creating an HTML file that Excel knows how to open and display to you inside a worksheet/workbook.
    

  So the first thing I would recommend is that you take off the ASIS=ON and see how that works. Generally, ASIS=ON is only used to preserve  leading blanks and there's no reason to do that, generally the browser or Excel will strip out the leading blanks anyway, and I remember that some versions of SAS, using ASIS=ON had the unwanted side effect of making the report look like it's "double spaced".
     

  The second thing I would recommend is that you should use "Microsoft-friendly" HTML. So, for example, Microsoft did not like the "standard W3C HTML 4" specification for HTML tags. They didn't like it a LOT. So they wrote their own flavor of HTML starting in Microsoft Office 2000. So SAS has a different destination to create Microsoft friendly HTML. It is the ODS MSOFFICE2K destination. I would recommend that you do this instead of ODS HTML:

ods listing close;

ods msoffice2K file="\............\Listings\Report\Site Enrollment\Site Enrollment Report_&sysdate9..xls" ;

...code without ASIS=ON...

ods msoffice2K close;

ods listing;

         

The third thing I recommend is that you change the file extension back to HTML (what the output really is) and look at the output in a browser to determine if ODS is making the "blank rows" or if Excel is misinterpreting something when it displays the HTML.
     

Otherwise, you might want to open a track with Tech Support. They can look at your data and your code together and see if they can figure out what's going on. I no longer have SAS 9.1.3 to test with, so I am not sure that I would get the same results with SAS 9.4.
    

cynthia

View solution in original post

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

Hi:

  It really comes down to what's in WORK.ALL. When you use ODS HTML, you are not, technically, creating an Excel file. You are creating an HTML file that Excel knows how to open and display to you inside a worksheet/workbook.
    

  So the first thing I would recommend is that you take off the ASIS=ON and see how that works. Generally, ASIS=ON is only used to preserve  leading blanks and there's no reason to do that, generally the browser or Excel will strip out the leading blanks anyway, and I remember that some versions of SAS, using ASIS=ON had the unwanted side effect of making the report look like it's "double spaced".
     

  The second thing I would recommend is that you should use "Microsoft-friendly" HTML. So, for example, Microsoft did not like the "standard W3C HTML 4" specification for HTML tags. They didn't like it a LOT. So they wrote their own flavor of HTML starting in Microsoft Office 2000. So SAS has a different destination to create Microsoft friendly HTML. It is the ODS MSOFFICE2K destination. I would recommend that you do this instead of ODS HTML:

ods listing close;

ods msoffice2K file="\............\Listings\Report\Site Enrollment\Site Enrollment Report_&sysdate9..xls" ;

...code without ASIS=ON...

ods msoffice2K close;

ods listing;

         

The third thing I recommend is that you change the file extension back to HTML (what the output really is) and look at the output in a browser to determine if ODS is making the "blank rows" or if Excel is misinterpreting something when it displays the HTML.
     

Otherwise, you might want to open a track with Tech Support. They can look at your data and your code together and see if they can figure out what's going on. I no longer have SAS 9.1.3 to test with, so I am not sure that I would get the same results with SAS 9.4.
    

cynthia

Phil0917
Fluorite | Level 6

Hi Cynthia,

  Thanks so much for your quick reply.  It seems like the ASIS=ON was indeed the culprit.  I ran the code removing this, and the problem went away.  I should explain that this code is actually inside a larger ODS PDF (to create a large PDF with several listings, and an separate Excel file for each listing at the same time).  That was the reason for the ASIS=ON.  However, I may be able to still get the desired result in the PDF without this option, so I will give it a shot.

Thanks again!!!

-Phil

Cynthia_sas
SAS Super FREQ

Glad it worked! But I still don't understand the reason for ASIS=ON. The ONLY reason that I know of for using it is to preserve leading blanks that are generally stripped out anyway for HTML and Excel. Not sure the leading blanks even make a showing in PDF. But at any rate, I'm glad it worked for you. Still recommend using Microsoft friendly ODS MSOFFICE2K because Microsoft really, really did not like the style info in the original HTML 4 spec by the W3C. I just find that styles work smoother with ODS MSOFFICE2K and Microsoft Office products.

cynthia

Phil0917
Fluorite | Level 6

Hi Cynthia,

  The larger PDF contains a summary page at the beginning, in which we have several levels of indentation shown.  The ASIS=ON was used in order to retain these indentations.  In the subsequent listings, the indentations were not necessary, but the DEFINE statements for the PROC REPORT were copied from the first page report.  I just never realized that this would cause a double row to be produced in the HTML output, but I am grateful for your advice (as it worked like a charm).  I will definitely start using the ODS MSOFFICE2K as well, though.  I have had some other formatting issues in the past with HTML, and I'm hoping that perhaps this will solve those as well.  Thanks so much again!!!

-Phil

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1209 views
  • 0 likes
  • 2 in conversation