ODS HTML Output 1

[OldGuy57]

When I generate html output using ODS and then open the results in Excel, cells are merged (e.g. d4 and d5 show up as one cell-d4). When I remove the merging, then values line up incorrectly, what should be in d5 ends up in d4.

Is there any way to create html output that will not show up with merged cells when opening in Excel? I know a bit about Excel and nothing about html.

 

[klaz2002]

Perhaps you can use ODS CSV instead.

 

[OldGuy57]

After finding this forum and reviewing the FAQs I found out about the csv format from the SAS support website. It works with regards to the problem of merged cells but the csv format drops some of the rows from the output, in particular some of the labels from the proc tabulate procedure.

It looks like with some tweaking of my proc tabulate options I may be able to get the csv format to work.

 

[ChrisW75]

There's also a tagset which allows you to write to an Excel spreadsheet, assuming you have SAS V9.
ods tagsets.excelXP
There's been some discussion on this here in the forums which you should be able to find easily enough.
Also it's reasonable well documented on the SAS Support site.
Basically though it's used like this:-

ods tagsets.excelxp file="P:\Clients\Active\xxxx\reports\xxxx_&RUNDT..xls"
    options(absolute_column_width='10,10,10,10,8,8,8,8,8,8,8,8,8,8'
            row_repeat='header'
            embedded_titles='yes'
            frozen_headers='yes'
            scale='100' 
            orientation='landscape'
            sheet_name='Report'
           )
   style=styles.psexcel;

proc report ....;
  blah blah blah;
run;

ods tagsets.excelxp close;

You can put anything you like in place of the proc report.
It also contains it's own documentation which you can get it to print like this:-

ods tagsets.excelxp file="c:\test.xls"
    options(doc='help');


ods tagsets.excelxp close;

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[OldGuy57]

Tagsets looks very promising, I need to go through the documentation looking for how to minimize any formatting. A quick first try still had the merged cells.

Thanks for the pointers!

 

[ChrisW75]

Ah yes! I get you now. I'm afraid that this is a problem that isn't going to go away. I haven't found a way around this yet.
If it bothers me, I manually move it after I've output it.
One possibility is to create a macro in Excel that'll do the job for you... Should reduce the effort required.
The good thing about using the tagsets is that you can set up a stylesheet using proc template that'll handle colours, fonts etc etc, so your output will correspond to corporate guidelines/preferences etc.
I even made an amendment to the ExcelXP tagset which allowed me to specify that a field was a text string rather than a numeric so that the leading zeroes didn't get trimmed off.
The info on how to do this is in a previous forum post on this site.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.