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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.