/*------------------------------------------------------------------------------

File: CopyDataSetsToWorkbook.sas

Author:         John Sabel
                Washington State Education Research & Data Center
Creation date:  2015-09-14
Version:        0.9

Purpose
	Copies all the data sets in a libname into an Excel workbook. Each data set
    is copied to its own worksheet. The name of each data set becomes the name
    of its respective worksheet.

Parameters
    ExcelPathIn: The full path of the destination Excel workbook.
    LibnameIn:   (Optional) The libref of the SAS library that contains the data
                 sets to be copied to Excel. The default is the WORK libref.
    RegExFilter: Regular expression to filter which data sets are copied to
                 Excel. The default, "/./" acts as no filter at all.
    Debug:       (Optional) When set to Y, the SAS log displays the parameter
                 values.

Signature
%CopyDataSetsToWorkbook(ExcelPathIn=, 
                        LibnameIn=WORK,
                        RegExFilter=/./, 
                        Debug=N)

------------------------------------------------------------------------------*/
%macro CopyDataSetsToWorkbook(ExcelPathIn=, LibnameIn=WORK, RegExFilter=/./, Debug=N);
	%put --- Start of %upcase(&sysmacroname) macro;
	%if &Debug.~=N %then %put _local_;

	%local DsnCount;
/*	%let DsnCount=0;*/

	proc sql %if &Debug=N %then noprint;;
		SELECT MEMNAME
		INTO :Dsn1 - :Dsn999
		FROM DICTIONARY.TABLES
		WHERE LIBNAME = "%upcase(&LibnameIn.)" AND
		      PRXMATCH("&RegExFilter.", MEMNAME) > 0
		;
		%let DsnCount=&sqlobs.;
	quit;

	%if &DsnCount.=0 %then %do;
		%put %str(WARN)ING: Libname &LibnameIn. contains no data sets. No workbook has been created.;
	%end;

	%do i = 1 %to &DsnCount.;
		proc export data=&&Dsn&I. 
			outfile="&ExcelPathIn." 
			dbms=xlsx replace; 
			sheet="&&Dsn&I.";
		run;
	%end;

	%if &Debug.~=N %then %put _local_;
	%put --- End of %upcase(&sysmacroname) macro;
%mend CopyDataSetsToWorkbook;
/*
* Sample code ;

%CopyDataSetsToWorkbook(ExcelPathIn=C:\Temp\DsnsInSasWorkLibref.xlsx, 
                        LibnameIn=WORK,
                        RegExFilter=/./, 
                        Debug=Y)
*/