BookmarkSubscribeRSS Feed
MZunnurain
Calcite | Level 5
Hello,

When I use ods html to create xls file, below, the var logfile is a long char string and it wraps in the cell and the column is too narrow, abt 35 when viewed in Excel.

proc print data=all u;
sum realtime / style={htmlstyle="vnd.ms-excel.numberformat:[hh]:mm:ss.00"};
var logfile ;
var stepname ;
var realtime / style={htmlstyle="vnd.ms-excel.numberformat:[hh]:mm:ss.00"};
var obsin obsout varsout stepcnt;
run;

When I use excelxp tagset, as in

ods tagsets.excelxp file=all.xls" style=whatever
options(
sheet_label=' ');

ods tagsets.excelxp options(
sheetname=' ');

proc print label data=all;
sum realtime / style={htmlstyle="vnd.ms-excel.numberformat:[hh]:mm:ss.00"};
var logfile ;
var stepname;
var realtime / style={htmlstyle="vnd.ms-excel.numberformat:[hh]:mm:ss.00"};
var obsin obsout varsout stepcnt;
run;
ods tagsets.excelxp close;

The long char string no longer wraps, but now the column is much too wide, abt 227 in the column width viewed in Excel.

The other column widths are acceptable.

Any ideas?
1 REPLY 1
Cynthia_sas
SAS Super FREQ
Hi:
When you use ODS HTML, you are not creating a "true, binary" .XLS file. Using the file extension .XLS with an ODS HTML program is just a convenient way to get the Windows registry to launch Excel when you double click on the file name. Otherwise, if you used the correct extension of .HTML, your browser would launch when you double clicked on the file name.

You can prove that you have an HTML file by finding the file that you created with ODS HTML and open the file with a text editor, such as Windows Notepad. You should see HTML tags in the file.

This means that your behavior (column widths and formats) are determined by Excel's rules for how to render an HTML file. In fact, when you use the HTMLSTYLE override in your code, you are trying to "influence" Excel's rendering by sending Excel some "mso-number-formats" to use.

If there is an "mso-" property for column widths, you would have to look in the Microsoft documentation to find it. But, even so, my experience with Microsoft Excel and "mso-" properties is that you might get a width you want in the browser, but possibly not in Excel.

If you would switch to Spreadsheet Markup Language XML -- as the result type -- this is a type of XML introduced in Office 2002/2003, then you would be able to use a sub-option for absolute_column_width in order to send Excel instructions about how to render the XML, as far as column widths go. There is a lot of documentation on ODS TAGSETS.EXCELXP (how ODS creates Spreadsheet Markup Language XML) in previous forum postings and you should be able to find lots of information by doing a search.

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
  • 1 reply
  • 1237 views
  • 0 likes
  • 2 in conversation