topic Re: ODS EXCEL Won't Automatically Adjust Row Height in SAS Programming

ODS EXCEL Won't Automatically Adjust Row Height

djbateman — Mon, 24 Jun 2024 18:37:48 GMT

My company creates xls output using ODS TAGSETS.EXCELXP. We want to export directly to xlsx using ODS EXCEL, but we have some limitations. We were excited when we finally upgraded from SAS 9.4 M3 to M6 thinking those limitations would be resolved, but not all were. We still have a lingering issue. In ODS TAGSETS, we can apply the AUTOFIT_HEIGHT='yes' option to make the row heights adjust based on the text. That option seems to have disappeared in ODS EXCEL. I know ODS EXCEL has ROW_HEIGHT and ABSOLUTE_ROW_HEIGHT, but they do not have the same functionality. I can use these to either speicfy the same height for all rows, or I can speicify the height of each row individually. This becomes ridiculously unfeasible when dealing with 1000s of records. This is a bit of a deal breaker because our text wraps, and we don't want wrapped text to be hidden and missed by a shortened row.

This is example code of how we use ODS TAGSETS, and we want to replicate this output with ODS EXCEL:

/***  Template intended for xls output  ***/
ODS PATH work.templat(update) sasuser.templat(read) sashelp.tmplmst(read);
proc template;
	define style work.newxls;
		parent=styles.normal;
		style default / fontsize = 14pt;
		style Data from Column /                                                
			bordertopwidth = 0px                                                 
			borderrightstyle = solid                                             
			bordertopcolor = #AAC1D9                                             
			padding = 3                                                          
			borderbottomcolor = #AAC1D9                                          
			borderbottomwidth = 1px                                              
			borderbottomstyle = solid                                            
			fontweight = medium                                                  
			borderrightwidth = 1px                                               
			verticalalign = _undef_                                              
			fontfamily = "<sans-serif>, <MTsans-serif>, Helvetica, sans-serif"   
			borderleftstyle = solid                                              
			borderrightcolor = #AAC1D9                                           
			borderleftcolor = #AAC1D9                                            
			textalign = _undef_                                                  
			fontsize = 10pt                                                       
			borderleftwidth = 0px                                                
			bordertopstyle = solid;                                              
		style Header from Column /                                              
			bordertopwidth = 0px                                                 
			borderrightstyle = solid                                             
			bordertopcolor = #AAC1D9                                             
			padding = 3                                                          
			borderbottomcolor = #AAC1D9                                          
			backgroundcolor = #faf3d4                                            
			borderbottomwidth = 1px                                              
			borderbottomstyle = solid                                            
			fontweight = bold                                                    
			borderrightwidth = 1px                                               
			verticalalign = middle                                               
			fontfamily = "<sans-serif>, <MTsans-serif>, sans-serif"              
			borderleftstyle = solid                                              
			borderrightcolor = #AAC1D9                                           
			borderleftwidth = 0px                                                
			borderleftcolor = #AAC1D9                                            
			textalign = center                                                   
			fontsize = 12pt                                                       
			color = cx000000                                                     
			bordertopstyle = solid;                                              
	end;
run;

/***  This code will use the NEWXLS template to generate xls output and properly uses AUTOFIT_HEIGHT and WRAPTEXT.  ***/
%let ods_options=%str(	row_repeat = 'header' 
						autofilter = 'yes'
						orientation = 'landscape'
						frozen_headers='5'
						center_horizontal = 'yes'
						fittopage = 'yes'
						pages_fitwidth = '1'
						pages_fitheight = '100'
						autofit_height = 'yes'
						embedded_titles = 'yes'
						embedded_footnotes = 'yes'
						wraptext = 'yes'
						gridlines = 'yes'
						sheet_interval = 'none');

ods listing close;
ods tagsets.excelxp file="c:\temp\Test_Output.xls" style=work.newxls;

title1 j=l "Vertex Pharmaceuticals Incorporated";
title2 j=l "Protocol: ";
title3 j=l "Test Report Demonstraiting Row Height - Written for SAS 9.4 M3";

ods tagsets.excelxp options (&ods_options. sheet_name='Test Case' absolute_column_width='12');
	proc report data=sashelp.aacomp nowindows split='^';
		column locale key lineno text;
		define locale /	'Short Variable' style(column)={tagattr='format:@'};
		define key /	'Medium Variable' style(column)={tagattr='format:@'};
		define lineno /	'Short Variable' style(column)={tagattr='format:@'};
		define text /	'Long Variable Text with a Long Label to Illustrate Wrapping' style(column)={tagattr='format:@'};
	run;

ods tagsets.excelxp close;
ods listing;

This is example code of how we would use ODS EXCEL, but we get can't find a replacement for AUTOFIT_HEIGHT. Notice how wrapped text is hidden from view:

/***  Template intended for direct xlsx output  ***/
ODS PATH work.templat(update) sasuser.templat(read) sashelp.tmplmst(read);
proc template;
	define style work.odsexcel;
		parent=styles.excel;
		class systemtitle/
			fontsize=12pt
			color=black; 
		class systemfooter/
			color=black; 
		class header/
		    fontsize=12pt
		    fontweight=bold 
			verticalalign=middle
		    textalign=center
			color=black
		    backgroundcolor=#faf3d4; 
		class data/
			fontsize=10pt
			verticalalign=_undef_
			textalign=_undef_;
	end;
run;


/***
	This code will use the ODSEXCEL template to generate xlsx output but does not allow the same options.
	WRAPTEXT is no longer an option. It produces a warning. But it can be replaced with the FLOW option.
	AUTOFIT_HEIGHT is no longer an option. It produces a warning. I can't find an equivalent option.
***/
%let ods_options=%str(	row_repeat = 'header' 
						autofilter = 'yes'
						orientation = 'landscape'
						frozen_headers='5'
						center_horizontal = 'yes'
						fittopage = 'yes'
						pages_fitwidth = '1'
						pages_fitheight = '100'
/*						autofit_height = 'yes'*/
						embedded_titles = 'yes'
						embedded_footnotes = 'yes'
/*						wraptext = 'yes'*/
						flow='tables'
						gridlines = 'yes'
						sheet_interval = 'none');

ods listing close;
ods excel file="c:\temp\Test_Output.xlsx" style=work.odsexcel;

title1 j=l "Vertex Pharmaceuticals Incorporated";
title2 j=l "Protocol: ";
title3 j=l "Test Report Demonstraiting Row Height - Written for SAS 9.4 M6";

ods excel options (&ods_options. sheet_name='Test Case' absolute_column_width='12');
	proc report data=sashelp.aacomp nowindows split='^';
		column locale key lineno text;
		define locale /	'Short Variable' style(column)={tagattr='format:@'};
		define key /	'Medium Variable' style(column)={tagattr='format:@'};
		define lineno /	'Short Variable' style(column)={tagattr='format:@'};
		define text /	'Long Variable Text with a Long Label to Illustrate Wrapping' style(column)={tagattr='format:@'};
	run;

ods excel close;
ods listing;

Please let me know how I can escalate this to be fixed. We are running into issues where xls files are too large to email, and we don't want to have to manually save them as xlsx files.

Re: ODS EXCEL Won't Automatically Adjust Row Height

Ksharp — Tue, 25 Jun 2024 01:34:59 GMT

Use WRAP:NO option to avoid wrap long text:
https://communities.sas.com/t5/SAS-Programming/PROC-ODSTEXT-excel-text-wrapping/m-p/933587#M367159

And you can make a RAR/ZIP file to compress this xls file by WINRAR before emailing it.

Re: ODS EXCEL Won't Automatically Adjust Row Height

djbateman — Tue, 25 Jun 2024 17:39:58 GMT

We actually want text to wrap. The problem is when it wraps, it gets hidden by row heights being too small. But I will investigate your suggestion to make a RAR/ZIP file for compressing.

Re: ODS EXCEL Won't Automatically Adjust Row Height

Ksharp — Wed, 26 Jun 2024 02:34:44 GMT

I am afraid you have to use "ABSOLUTE_ROW_HEIGHT" option in ODS EXCEL or try CELLWIDTH= CELLHEIGHT= style.
Or @Cynthia_sas could give you a better/smart idea.

Re: ODS EXCEL Won't Automatically Adjust Row Height

SASKiwi — Wed, 26 Jun 2024 02:47:35 GMT

ODS TAGSETS.EXCELXP still works in SAS 9.4M6 if you find ODS EXCEL doesn't provide the compatibility you want.

Re: ODS EXCEL Won't Automatically Adjust Row Height

Cynthia_sas — Wed, 26 Jun 2024 17:24:50 GMT

Thanks @Ksharp for your faith in me, but without data to be able to run the code and actually see what's happening, I can't do much. However, since this is a question about the difference between using ODS TAGSETS.EXCELXP and ODS EXCEL, since they are 2 different Microsoft XML formats, I think the best resources for this question would be to open a case with Tech Support so they get to the ODS experts in TS.
Cynthia

Re: ODS EXCEL Won't Automatically Adjust Row Height

data_null__ — Wed, 26 Jun 2024 17:59:22 GMT

@djbateman wrote:

Please let me know how I can escalate this to be fixed. We are running into issues where xls files are too large to email, and we don't want to have to manually save them as xlsx files.

https://communities.sas.com/t5/SAS-Programming/Tagsets-ExcelXP-to-XLSX/m-p/91177#M19279

If you can't find the solution you are looking for with ODS EXCEL you could use EXCELXP and use the VBSCRIPT in the link to convert the XLS to XLXS. Assuming you are using SAS on windows.

Re: ODS EXCEL Won't Automatically Adjust Row Height

djbateman — Wed, 26 Jun 2024 19:03:54 GMT

Thank you for your suggestion. That is actually exactly what we are doing. However, the VBscript will not work with our task scheduler (it just cuts out as soon as we initiate the VBscript), so we have to manually run some of our programs every day. We try to use the xls files from ODS TAGSETS when we can, but sometimes the files get too large to convert from xml to xlsx and then we run into the task scheduler issue. We would like either a solution on how to get the VBscript to run with Task Scheduler or how to fix the row height issue in ODS EXCEL. I figured the row height issue would be the easiest route, but maybe not.

Re: ODS EXCEL Won't Automatically Adjust Row Height

djbateman — Wed, 26 Jun 2024 19:05:01 GMT

FYI, my code above uses the AACOMP dataset in the SASHELP library. Is that data sufficient to play around with?

Re: ODS EXCEL Won't Automatically Adjust Row Height

Cynthia_sas — Thu, 27 Jun 2024 16:37:17 GMT

Hi:

Sorry, my bad. I didn't notice that you were using SASHELP. In the code example below I was not comparing TAGSETS.EXCELXP to ODS EXCEL. I was only testing your statement about ODS EXCEL and row height. My experience with PROC REPORT and taking ALL the defaults, is that ODS EXCEL will cause Excel to change the row height based on the cellwidth value. I did not use your template and I did not use your OPTIONS except for sheet_name. I wanted to get as vanilla an example as possible.

First I fiddled until I got all most of the rows to be just one row high so that any wrapped values for the TEXT variable would be obvious. Then I started the PROC REPORT using FIRSTOBS and OBS to cut down the amount of scrolling to see the first occurrence of row height adjusting, as shown below:

In this screen shot, you can see row height increase for the row highlighted in yellow.

Next the only change I made other than changing the name of the output file was to alter CELLWIDTH=4in for the TEXT variable. That forced more wrapping and row height adjusted, as shown below:

Now you can see in the same group of rows, that more rows for TEXT are wrapping and the row height is adjusting as expected.

This is why I think that you need to work with Tech Support on this question. I can prove that ODS EXCEL in this scenario does adjust row height as I expect. I think I've said other places that rather than fiddling with some of the absolute and autofit options in ODS Excel-based destinations, I prefer to rely on CELLWIDTH so that I am only changing one attribute (width) and then not confusing things for Excel by adding in an option for row height that might conflict when Excel gets around to rendering the XML version of the report. I don't do a lot of template changes for ODS EXCEL. I'm fairly certain you'd need a new style template to use with ODS EXCEL because my guess is that a template that works with TAGSETS.EXCELXP may not work for every attribute with ODS EXCEL.

Hope this helps at least show that in some cases, the row height does adjust correctly when you use ODS EXCEL.

Cynthia

Re: ODS EXCEL Won't Automatically Adjust Row Height

djbateman — Thu, 27 Jun 2024 19:21:28 GMT

We do use ODS TAGSETS, but the problem is that is produces xls files that are far too large, so large that we can't attach them to emails. Because of this, we have a %convert_files macro where we use ODS TAGSETS to create an xml file and push that through the %convert_files macro. The issue we run into there is that Task Scheduler will not run that macro. It has something to do with the fact that the macro just write VBscript. Because of that, we have to manually run our report each morning. We are trying to find a way to either get %convert_macros to work with Task Scheduler, or a simple way to get SAS to allow row height to automatically adjust in ODS EXCEL. Both seem like they should be simple fixes, but neither one seems to exist.

Re: ODS EXCEL Won't Automatically Adjust Row Height

Cynthia_sas — Fri, 28 Jun 2024 01:48:54 GMT

Hi:

I don't know whether you saw it or not -- I posted an example of ODS EXCEL automatically adjusting row height based on cellwidth using the same SASHELP dataset that you used. It was earlier in the day so you may have missed it. I believe that if you start with my stripped down code as an example and gradually add options with ODS EXCEL, you may either find a combination of options that works or else you'll find out which option is breaking and you can report it to Tech Support.

Cynthia

Re: ODS EXCEL Won't Automatically Adjust Row Height

Cynthia_sas — Fri, 28 Jun 2024 02:13:28 GMT

Hi:

An additional point of information. ODS TAGSETS.EXCELXP is NOT creating a true binary XLS file. ODS TAGSETS.EXCELXP is only creating an Excel XML file as defined in the Microsoft XML standard that was the release BEFORE the XLSX release of Excel (and the DOCX and PPTX releases for their Office Suite). So when you use ODS TAGSETS.EXCELXP, the actual correct file extension you can use is .XML and then the downside of that naming convention is that you might have to open Excel file first and then open the XML file from the File --> Open menu in Excel.

There are even some versions of Excel that will throw an error message if you try to click on the XLS extension that you use with TAGSETS.EXCELXP because of a mismatch between Excel expecting true binary XLS format in the file but finding the XML format instead.

However, ODS EXCEL creates a true XLSX file, which is a multi-file archive that Excel knows how to render when you click on the XLSX file to open it into Excel.

If you do this use ODS TAGSETS.EXCELXP on a simple PROC PRINT of SASHELP.CLASS and name the output file as XLS, if you look at the XLS file with Notepad, this is what you see:

The file is created as XML, using these Microsoft schemas:

When you change the code to use ODS EXCEL and you look at the results with Notepad, this is what you see:

However when you click to open the XLSX file with Excel, you see that the file is rendered in Excel as you expect:

One trick to see that the ODS EXCEL destination is creating a true format XLSX file is to go to File Explorer and rename the file with a .ZIP extension and then open the renamed file with WinZip and you will see the view on the right of the contents of the XLSX file. The format is the archive version of the XLSX file that conforms to the Microsoft Open Office XML or mayb it's Microsoft Office Open XML. I can't remember which O comes first.

Cynthia

Re: ODS EXCEL Won't Automatically Adjust Row Height

djbateman — Fri, 28 Jun 2024 14:38:58 GMT

Thank you, Cynthia. This is not a perfect solution, but it is a great start. I'll play around with this and see if I can get it to work for our company's needs.