Desktop productivity for business analysts and programmers

Multisheet excel export column width specification

Reply
New User
Posts: 1

Multisheet excel export column width specification

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

 

Super User
Posts: 23,980

Re: Multisheet excel export column width specification

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. 

Ask a Question
Discussion stats
  • 1 reply
  • 338 views
  • 0 likes
  • 2 in conversation