Excel to SAS

SASPhile — Wed, 09 Dec 2009 18:56:42 GMT

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.;

Re: Excel to SAS

Cynthia_sas — Wed, 09 Dec 2009 19:25:07 GMT

Hi:
If you were using the LIBNAME engine connection to Excel, I'd recommend these:
http://www2.sas.com/proceedings/sugi31/034-31.pdf
http://www2.sas.com/proceedings/sugi31/024-31.pdf

because both papers have good information about naming conventions.

I do not know whether the other methods (JET/ ODBC/OLE-DB) use the same sheetname methods as the LIBNAME engine. This might be a question for Tech Support, if you do not get any other suggestions from forum participants.

cynthia

topic Excel to SAS in SAS Programming

Excel to SAS

Re: Excel to SAS