topic Re: Export all datasets from a library to single excel file in SAS Programming

Export all datasets from a library to single excel file

abraham1 — Mon, 16 Nov 2020 16:54:59 GMT

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);

Re: Export all datasets from a library to single excel file

Kurt_Bremser — Mon, 16 Nov 2020 17:15:21 GMT

This very easy with LIBNAME XLSX:

libname outex xlsx "your_excel_file.xlsx";

proc copy in=inlib out=outex;
run;

Re: Export all datasets from a library to single excel file

abraham1 — Mon, 16 Nov 2020 17:31:17 GMT

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

Re: Export all datasets from a library to single excel file

Reeza — Mon, 16 Nov 2020 17:33:58 GMT

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

Re: Export all datasets from a library to single excel file

Tom — Mon, 16 Nov 2020 19:15:04 GMT

@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.

Re: Export all datasets from a library to single excel file

ballardw — Mon, 16 Nov 2020 22:51:17 GMT

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.