Desktop productivity for business analysts and programmers

Autofit using Proc Export

Posts: 0

Autofit using Proc Export

Can someone tell me how to autofit columns in Excel using PROC Export?

Here is what I have right now
DATA = mydata
Outfile = "C:\Documents and Settings\MyDocuments\EXPORT\Mydocument"
SHEET= "mysheet";

The results of this in Excel goes to Excel's default column width, I need to size the columns with the size of the data. Is this possable?
Posts: 8,720

Re: Autofit using Proc Export

Proc Export creates a basic .XLS file. As far as I know, you cannot issue Microsoft Excel "menu" commands such as autofit from anywhere in PROC EXPORT syntax.

However, if you switched to using ODS TAGSETS.EXCELXP, you CAN specify column widths or a default column width for when the file is opened in Excel. This works because ODS TAGSETS.EXCELXP creates a Spreadsheet Markup Language XML file which contains a syntax for specifying column widths. This is different from PROC EXPORT, which exports SAS data to Excel binary format, but without any formatting instructions (such as font, color or column widths). TAGSETS.EXCELXP, on the other hand, has a way to specify DEFAULT_COLUMN_WIDTH, as well as ABSOLUTE_COLUMN_WIDTH -- which isn't exactly the same as AUTOFIT, but it does get you a bit closer to making columns wider when they need to be.

There's a chance that if you used OLE-DB or ODBC or DDE instead of PROC EXPORT, that there is also a way to specify column widths with those methods; however, I haven't done enough with those methods to know.

here's a sample ODS TAGSETS.EXCELXP example using Default_Column_Width:
ods tagsets.excelxp file='c:\temp\datafile.xls'
options(Doc='Help' Default_Column_Width='20')

** proc print step for your data file;

ods _all_ close;

This code will produce HELP information for TAGSETS.EXCELXP in the SAS log. For more information about TAGSETS.EXCELXP and the available options refer to this site:
Frequent Contributor
Posts: 91

Re: Autofit using Proc Export

I have successfully fit columns, formatted ranges, etc using DDE, but it is certainly not for the faint of heart or anyone under significant time constraints.

Here is some example code:
/* assumes that Excel is already running */
filename cmds dde 'excel|system';

data _null_;
file cmds;
put "[OPEN(""C:\Temp\Experiment.xls"")]";
put "[ERROR(""FALSE"")]";
put "[workbook.delete(""Place_holder"")]";
put "[ERROR(""TRUE"")]";
put "[""class"")]"; /* select sheet */
put "[select(""r2c3:r20c3"")]"; /* select all of column 3 */
put "[format.number(""yyyy/mm/dd"")]"; /* format as yyyymmdd */
put "[select(""r2c4:r20c5"")]"; /* select columns 4 and 5 */
put "[format.number(""#,##0.00"")]"; /* format with 2 decimals and comma*/
put "[select(""r1c1:r1c5"")]"; /* select headers */
put "[,""Bold"")]"; /* make them bold */
put "[alignment(3,""TRUE"",3,0)]"; /* center,wrap,bottom,no orientation */
put "[SELECT(""R1C2"")]"; /* select one header */
put "[alignment(,""FALSE"",,1)]"; /* no wrap, vertical orientation */
put '[COLUMN.WIDTH(1,"C1:C5",,3,1)]'; /* autofit columns 1 thru 5 */
put '[ERROR("TRUE")]';
put '[ERROR("FALSE")]';
put '[SAVE()]';
put '[ERROR("TRUE")]';
put '[CLOSE("FALSE")]';

filename cmds clear;
Frequent Contributor
Frequent Contributor
Posts: 75

Re: Autofit using Proc Export

I realize this post is several years old and I don't care.

put '[COLUMN.WIDTH(1,"C1:C5",,3,1)]';  rocks and made me an office god this morning.

Thank you.

Ask a Question
Discussion stats
  • 3 replies
  • 1 like
  • 4 in conversation