Autofit using Proc Export

[deleted_user]

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

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

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?

[Cynthia_sas]

Hi:
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.

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

** proc print step for your data file;

ods _all_ close;
[/pre]

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:
http://support.sas.com/rnd/base/ods/odsmarkup/
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

[advoss]

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 "[workbook.select(""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 "[font.properties(,""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")]';
run;

filename cmds clear;

[HB]

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.