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
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.