BookmarkSubscribeRSS Feed
pflickner
Fluorite | Level 6

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. 🙂

 

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.

 

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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

pflickner
Fluorite | Level 6

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. 🙂 This is what I do when I get bored. Thanks!

pflickner
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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
  • 3 replies
  • 5980 views
  • 0 likes
  • 2 in conversation