read_excel_into_cas

/*
Important: Please consider this code as a benchmark. It perfectly works in my machine for my purposes. But maybe has to be updated for your needs. Thanks. Do not hesitate to contact me.
_dir = path to your excel files;
_libin = library with xlsx engine;
_libout = cas library for the output;
_excel_name = name of the excel to be imported;
_tables = spreadsheet/table name;
memcount = number of objects in the folder with excel files; 

Remember when you read excel file into sas -> there is a difference between excel name file and spreadsheet name.
As an input in libname user defines excel name, but in the input library user will see as table names excel spreadsheet names

Author: Wiktor Byrda
viktar.byrda@gmail.com
*/

%let _dir = ;
%let _libin = ;
%let _libout = ;

%macro _read_excel_into_cas();

%let i=1;
%let rc=%sysfunc(filename(main_dir, &_dir.));
%let did=%sysfunc(dopen(&main_dir));
%let memcount=%sysfunc(dnum(&did)); 

 %if (&memcount > 0) %then %do; 
	%do i=&i. %to &memcount.; 

		%let _excel_name= %sysfunc(dread(&did, &i.)); 
		%let _name = %scan(&_excel_name., 1, '.xlsx');
		%put &=_excel_name &=_name &=did &=memcount &=main_dir &=RC;

		libname &_libin. xlsx "&_dir./&_excel_name.";


			proc sql noprint;
				select memname into :_tables separated by " "
				from sashelp.vtable
				where libname = "&_libin."
				;
			quit;

		%do j=1 %to %sysfunc(countw(&_tables));
		%let _next_table = %scan(&_tables, &j);

		proc casutil;
		load data=&_libin..&_next_table.
		casout="&_next_table."
		outcaslib="&_libout."
		replace
		;
		run;
		%end;

   %end;
  %end;
%let rc=%sysfunc(dclose(&did));
%mend;

%_read_excel_into_cas;