excel and sas

[deleted_user]

Hello,

I need SAS to look into a directory for a number of excel files, open one at a time, grab data from cells (by dde) and close it and move on.

I know how to access one file at a time with DDE triplet but not sure how to automate it with a number of files (macro, maybe?) also is there a way I can close the file once I'm done extracting? (ie: x 'abc.xls' to start/open excel, but how do i close it? I think it will be an issue when I process hundreds of files.)

also I would like to use a part of the name of the opened file (eg. 1234565-abc-def.xls - then use '1234565' as a value of the variable 'ID') as a variable in the new dataset - how can I do this??

Thank you very much for your support.
James

[wensui]

james,
what you describe is very easy. I've been always doing so.
to close the excel, you can use DDE to close either workbook or quit excel application. very easy.
to loop through all excel files, you can first use pipe to read the file name into a table and then use macro to loop each record in the table.

[deleted_user]

James

since you are using DDE, you are writing datasteps with infile statements. If your data in these excel workbooks will have consistent structure(column order and informats), you could use one input statement combined with an infile statement like:

infile dumx device= dde filevar= next_trip dsd dlm='09'x lrecl= 10000 ....... ;

You just need to ensure that when the infile statement is executed, the variable next_trip will contain the dde triplet through which you next want to read from excel.
(and that the workbook is open).

Personally, I prefer to insert range names for each set of excel data I want to load, and use libraries for each workbook.

libname bk1 'my workbook1.xls' access= readonly ;
proc copy in=bk1 out= work mt= data ; run;

Although it might appear like more work, it is little more than verifying that each data appears OK.
It also removes the need to have data step code to perform the excel open() and close() of the relevant workbooks.

But, surely E.G. should relieve you of the need to use DDE ?

PeterC