SAS Export multiple datasets in work folder to multiple Excel workbooks with same names as datasets

I would like to export all the datasets that I have within the work folder in SAS to corresponding excel workbooks/separate excel files. So number of SAS datasets = number of the excel workbooks/files.

The excel workbooks should have the same names as the dataset names. And the destination directory would be in Z:\

Any macros for this would be highly appreciated.

Solution 1:

No need for any macro code (although you might want to use a macro variable to set the target directory).

Just use SASHELP.VMEMBER to get the list of datasets and then use CALL EXECUTE() to generate a PROC EXPORT step for each one.

data _null_;
  set sashelp.vmember;
  where libname='WORK' and memtype='DATA';
  call execute(catx(' '
   ,'proc export dbms=xlsx replace data=',nliteral(memname)
   ,'file=',quote(cats('Z:\',memname,'.xlsx'))
   ,';run;'
  ));
run;