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

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
collinelliot
Barite | Level 11

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.

View solution in original post

10 REPLIES 10
collinelliot
Barite | Level 11

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.

Rajeeva
Calcite | Level 5

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. 

 

collinelliot
Barite | Level 11

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;
Rajeeva
Calcite | Level 5

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.

collinelliot
Barite | Level 11

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.

 

 

Rajeeva
Calcite | Level 5

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

collinelliot
Barite | Level 11

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.

Rajeeva
Calcite | Level 5

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:@'};
            

collinelliot
Barite | Level 11

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;

 

 

Cynthia_sas
SAS Super FREQ
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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 10 replies
  • 3004 views
  • 0 likes
  • 3 in conversation