Hi Team,
how can you export 50 data sets into single excel workbook with different sheets.
Advance Thanks
Are the data sets in the same library? And how do you determine the names of the data sets?
yes
There are a few examples to get you going here
Please use more descriptive subject lines in the future. "sas" is NOT descriptive.
You can try the below. Just change the OUTFILE= with the full path of the file (in which you want all the results to go). Please note this macro may not work if you have more than 255 sheets to be exported. LIBNAME is the library/folder where all your sas datasets are there.
%MACRO ExportAllDatasetsToExcel(LIBNAME, Extension="XLS");
PROC SQL NOPRINT;
CREATE TABLE columns as
SELECT DISTINCT(memname) AS table_name FORMAT=$30.
FROM dictionary.columns
WHERE libname = &LIBNAME
;
quit;
PROC SQL NOPRINT;
SELECT COUNT(*) AS NumObs INTO :NumObs FROM columns;
RUN;
%DO I=1 %TO &NumObs;
DATA _NULL_ ;
SET Columns (FIRSTOBS=&I OBS=&I);
CALL SYMPUT('TableName',TRIM(table_name));
RUN;
%PUT "&TableName";
PROC EXPORT DATA=&LIBNAME..&TableName DBMS=XLSX OUTFILE="FullPathOfTheFileHere.xlsx";
SHEET="&TableName";
RUN;
%END;
%MEND;
Please let me know if this worked for you. I am just curious.
Thanku you
Shouldn't this work?
libname XL xlsx "%sysfunc(pathname(WORK))\T.xlsx";
proc copy inlib=WORK outlib=XL mt=data; run;
@ChrisNZ wrote:
Shouldn't this work?
libname XL xlsx "%sysfunc(pathname(WORK))\T.xlsx";
proc copy inlib=WORK outlib=XL mt=data; run;
Works perfect, with one minor glitch:
WARNING: Engine XLSX does not support SORTEDBY operations. SORTEDBY information cannot be copied.
@ChrisNZ Works very nicely. Your code is simple and elegant.
I read earlier but I forgot @Kurt_Bremser advice (his Maxim 11 of 52 ... that a Macro is not needed) and the result is a negative control of an experiment (wherein the positive control being someone pursuing reuse of existing code and elegance).
Lesson: Use BASE SAS first. So much is already there. Perhaps many times, one can spare reinventing the wheel.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.