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

[ranikeka]

Hi

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

[Kurt_Bremser]

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

[ranikeka]

Hi

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

[Kurt_Bremser]

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.

[ranikeka]

Thanks a lot.

[andreas_lds]

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.

[Ksharp · Accepted solution]

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;

[ranikeka]

Great and many thanks 🙏