topic Re: Export multiple SAS datasets to one excel file in multiple sheets. in New SAS User

Export multiple SAS datasets to one excel file in multiple sheets.

ranikeka — Thu, 26 Nov 2020 11:02:19 GMT

Hi

I would like to Export multiple SAS datasets to one excel file in multiple sheets. Please help with any macro code ?
Thanks

Re: Export multiple SAS datasets to one excel file in multiple sheets.

Kurt_Bremser — Thu, 26 Nov 2020 11:23:16 GMT

Define "multiple datasets". Are these datasets in different libraries, or all in one library? Do they share a common naming structure (e.g. all dataset names start with the same string)?

Re: Export multiple SAS datasets to one excel file in multiple sheets.

andreas_lds — Thu, 26 Nov 2020 11:37:09 GMT

I am surprised that non of the solution already posted for this problem fits your needs.

If you have AccessTo PcFiles licensed, maybe libname + proc copy is all you need. You won't need a macro, most likely.

Re: Export multiple SAS datasets to one excel file in multiple sheets.

Ksharp — Thu, 26 Nov 2020 12:03:23 GMT

No macro need.
If all your sas dataset are under the same fold :

libname x xlsx 'c:\temp\want.xlsx';
libname data v9 'd:\sas\';
proc copy in=data out=x;
run;

Re: Export multiple SAS datasets to one excel file in multiple sheets.

ranikeka — Thu, 26 Nov 2020 12:10:31 GMT

Hi

Yes they all in same library and with same structure name_a,name_b etc..
Thanks

Re: Export multiple SAS datasets to one excel file in multiple sheets.

Kurt_Bremser — Thu, 26 Nov 2020 12:51:50 GMT

First select the names:

proc sql noprint;
select memname into :dsnames separated by " "
from dictionary.tables
where libname = "LIBRARY" and memname like '%NAME';
quit;

Then copy to Excel:

libname ex xlsx "/path to your Excel file";

proc copy in=library out=ex;
select &dsnames.;
run;

or do it in one step with a wildcard:

libname ex xlsx "/path to your Excel file";

proc copy in=library out=ex;
select name:;
run;

The first selection method allows you to use more complicated selection rules.

Re: Export multiple SAS datasets to one excel file in multiple sheets.

ranikeka — Thu, 26 Nov 2020 13:03:53 GMT

Great and many thanks 🙏

Re: Export multiple SAS datasets to one excel file in multiple sheets.

ranikeka — Thu, 26 Nov 2020 13:16:24 GMT

Thanks a lot.