If there are multiple worksheets for each month, How to select the worksheet based on the year month in the filename.
For instance, my input file National Cornerstone 2009 09.xls,has worksheets from Jan to sep with naming convention as Jan 2009 to Sep 2009.How to select Sep 2009 worksheet?
and for National Cornerstone 2009 09.xls, how to select Oct 2009 worksheet?
To give heads up I use the following code:
proc sql;
connect to excel (path="&inpt.\National Cornerstone 2009 09.xls");
create table tabnaes as
select table_name from connection to excel (jet::tables);
quit;
The table tabnaes will have all the worksheet names.The only way is to hardcode the woksheet names to delete as shown below.
proc sql;
delete from tabnaes where table_name like '%FEIBA%' or table_name like '%$%';
select table_name into:wksht from tabnaes;
quit;
%let wksht = %trim(&wksht);
%put &wksht.;