The SAS Output Delivery System and reporting techniques

Turn Old ODS HTML to XLS to XLSX

Reply
Occasional Contributor
Posts: 10

Turn Old ODS HTML to XLS to XLSX

We currently have a process that creates ugly old XLS reports. I'm trying to show that it's easy to upgrade to XLSX, but I apparently need a little assistance. Smiley Happy

 

Right now, we use the following along with proc report:

 

ods html file = "detail_netdown_report.xls"
HEADTEXT="<STYLE> TD {MSO-NUMBER-FORMAT:\@}</STYLE>";

The problem is, I can't just change the "XLS" to "XLSX" (yes, I tried - I was hoping it was that simple). Can you recommend something better? I also tried using ODS EXCEL, but there was no success at even creating the spreadsheet. I've only used ODS EXCEL with proc print, so I don't know if there's something special I need and I've been playing with different options as well to no avail. Thanks in advance for any assistance.

 

SAS Super FREQ
Posts: 8,868

Re: Turn Old ODS HTML to XLS to XLSX

[ Edited ]
Posted in reply to pflickner

Hi: MSO-NUMBER-FORMAT was how you told ODS HTML to send a "microsoft format" to Excel when Excel opened the HTML file.

Since you are really creating an HTML file and NOT a true, binary .XLS file, it would NOT be as simple as changing the file extension to .XLSX.

You said you tried using ODS EXCEL but there was no success. What was the error message? if you are running SAS 9.4, you should have been able to use PROC PRINT or PROC REPORT or just about any procedure with ODS EXCEL.

What your HEADTEXT was doing was telling Excel to treat ALL the data cells as "text" cells. So even a number in a cell would get treated as a character string.

If you had been using ODS MSOFFICE2K, you would NOT have needed the HEADTEXT at all and would ONLY have needed to put your override in your PRINT, TABULATE or REPORT code.

With ODS TAGSETS.EXCELXP and ODS EXCEL, you need to get rid of the HEADTEXT= completely, that won't work and switch to TAGATTR.

Here's an example below. If that doesn't work for you and you are running 9.4, then you might want to open a track with Tech Support.

 

ods excel file = 'c:\temp\test_use_ms_fmt.xlsx' style=htmlblue;

proc print data=sashelp.class;
  var name sex;
  var age / style(data)={tagattr='Format:## Type:Text'};
  var height weight  /style(data)={tagattr='Format:###.# Type:Text'};
run;


proc report data=sashelp.class
  style(column)={tagattr='Type:Text'};
  column name sex age height weight;
run;

proc report data=sashelp.class;
  define name / display;
  define sex / display;
  define age / display style(column)={tagattr='Format:## Type:Text'};
  define height  / display style(column)={tagattr='Format:###.# Type:Text'};
  define weight  /display style(column)={tagattr='Format:###.# Type:Text'};
run;
ods _all_ close;

When I ran this code, I got one workbook with 3 sheets and in each sheet, the numbers for AGE, HEIGHT and WEIGHT were formatted as TEXT, which you can tell because the little green triangle is in the upper left hand corner of the data cell.

cynthia

Occasional Contributor
Posts: 10

Re: Turn Old ODS HTML to XLS to XLSX

Posted in reply to Cynthia_sas

I should have included the code I used (this is what I used with my successful spreadsheet creation):

    ods _all_ close;
    options obs=max ps=5000 noquotelenmax;
    goptions device=png;
    ods noproctitle escapechar="~";
    ods excel file="netdown_report.xlsx";

I got no errors at all during the run, but it never created the spreadsheet. I will take a look at what you did here and see how that works for me. I also should have said I'm running this on Linux. I'm also currently working on creating a pivot table for our spreadsheets created on Linux since SAS has no way of creating the pivot table outside of Windows unfortuantely. Smiley Happy This is what I do when I get bored. Thanks!

Occasional Contributor
Posts: 10

Re: Turn Old ODS HTML to XLS to XLSX

Posted in reply to pflickner

I don't know why, but that did it! Thanks, Cynthia!

Ask a Question
Discussion stats
  • 3 replies
  • 474 views
  • 0 likes
  • 2 in conversation