Export all datasets from a library to single excel file

[abraham1]

Hi,

The below code is working when I export all datasets into multiple excel file in a specified location but If I want to save all the output in a single excel file with multi-sheet output, where should I change the program. I tried all possible way but its not working.

%macro SASToExcel(ExportLibrary=);
%* obtain list of datasets in the library;
    ods output members = _Members;
    proc datasets lib = &ExportLibrary; run; quit;
    proc sql;
*get the name of datasets into the list using Dataset1, Dataset2 etc;
        select Name into :Dataset1-
from _Members;
    quit;
*store the number of records to control the loop;
%let NumOfDatasets=&sqlobs;
%*loop to export all your data sets;
    %do index = 1 %to &NumOfDatasets;
		proc export data=&ExportLibrary..&&Dataset&index.
        outfile="/GST/COUNCIL/COUNTRY/Program/&&dataset&index..xlsx"
        dbms=XLS replace;
        run;
    %end;
%mend;
%SASToExcel(ExportLibrary=work);

[Kurt_Bremser]

This very easy with LIBNAME XLSX:

libname outex xlsx "your_excel_file.xlsx";

proc copy in=inlib out=outex;
run;

[abraham1]

Thank you for the suggestion. As the variable label is more than 50 characters, I am getting warning message as truncation happening during exporting like below. I just wanted to learn how macro work as a newbie. Thanks once again

NOTE: The variable label Employee information in Columbia state of America has been truncated to Employee information

[Tom]

---

@abraham1 wrote:
Thank you for the suggestion. As the variable label is more than 50 characters, I am getting warning message as truncation happening during exporting like below. I just wanted to learn how macro work as a newbie. Thanks once again

NOTE: The variable label Employee information in Columbia state of America has been truncated to Employee information

---

Not clear why it gives that message. An XLSX file does not have any concept of a variable label.

[ballardw]

It might help to include the code along with all the notes, warnings and/or errors generated.

Copy the entire procedure step with all the notes from the log and then paste into a code box on the forum opened with the </> icon to preserve formatting of text and any diagnostic characters.

[Reeza]

And you really shouldn't be using XLS files anymore. If you need any more reasons for switching off, here's one example.
https://www.bbc.com/news/technology-54423988