Multisheet excel export column width specification

User01 — Sat, 21 Oct 2017 01:20:10 GMT

I am exporting a data set into their own separate multisheet excel file in SAS EG 6.1. I would like to have the columns in the sheets either auto width based on cell values or specify the width. From what I have found, I believe I need to use an ODS tagset.EXCELXP but I'm not entirely sure how to accomplish this. Below is the program I have been using to export. What do I need to add to adjust the columns?

PROC SQL;

CREATE TABLE TABLE_ONE AS

SELECT

ELEMENT_ONE,

ELEMENT_TWO,

ELEMENT_THREE,

IDENTIFIER

FROM WORK.QUERY_FOR_APPEND_TABLE_01;

QUIT;

PROC SQL;

CREATE TABLE TABLE_TWO AS

SELECT

ELEMENT_FOUR,

ELEMENT_FIVE,

IDENTIFIER

FROM WORK.QUERY_FOR_APPEND_TABLE_02;

QUIT;

PROC SQL;

CREATE TABLE TABLE_THREE AS

SELECT

ELEMENT_SIX,

ELEMENT_SEVEN,

ELEMENT_EIGHT,

ELEMENT_NINE,

IDENTIFIER

FROM WORK.QUERY_FOR_APPEND_TABLE_03;

QUIT;

%macro multisheet;

proc sql noprint;

select distinct IDENTIFIER

into :IDENTIFIER1 - :IDENTIFIER92

from TABLE_ONE;

%let leacnt = &sqlobs;

%let now1=%sysfunc(today(),yymmddn8.);

quit;

%do i = 1 %to &leacnt;

PROC EXPORT DATA = TABLE_ONE(where=(IDENTIFIER=&&IDENTIFIER&i))

OUTFILE="OUTPUT_FILE_&IDENTIFIER&i.._&now1;.xls"

DBMS= excelcs REPLACE; PORT=0000; server=XXXX;

sheet = "TABLE_ONE";

RUN;

PROC EXPORT DATA = TABLE_TWO(where=(IDENTIFIER=&&IDENTIFIER&i))

OUTFILE="OUTPUT_FILE_&IDENTIFIER&i.._&now1;.xls"

DBMS= excelcs REPLACE; PORT=0000; server=XXXX;

sheet = "TABLE_TWO";

RUN;

PROC EXPORT DATA = TABLE_THREE(where=(IDENTIFIER=&&IDENTIFIER&i))

OUTFILE="OUTPUT_FILE_&IDENTIFIER&i.._&now1;.xls"

DBMS= excelcs REPLACE; PORT=0000; server=XXXX;

sheet = "TABLE_THREE";

RUN;

%END;

%mend multisheet;

%multisheet

Re: Multisheet excel export column width specification

Reeza — Sat, 21 Oct 2017 01:24:44 GMT

PROC EXPORT doesn't allow you to control the column width or the presentation. For that you need to use either ODS Excel or ODS Tagsets.ExcelXP.

https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

Most ODS TAGSETS things are relevant to ODS EXCEL as well. Unfortunately this isn't a straight forward as change a setting, but it's not super complicated either.

topic Re: Multisheet excel export column width specification in SAS Enterprise Guide

Multisheet excel export column width specification

Re: Multisheet excel export column width specification