DATA Step, Macro, Functions and more

Excel to SAS

Reply
Super Contributor
Posts: 647

Excel to SAS

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.;
SAS Super FREQ
Posts: 8,743

Re: Excel to SAS

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
Ask a Question
Discussion stats
  • 1 reply
  • 107 views
  • 0 likes
  • 2 in conversation