Question about using proc import to read multiple spreadsheets from an excel file

[kellyA]

I need to read in a ~40 spreadsheet excel file.  I found this macro posted on another forum, which has been incredible helpful-

%macro imp (insheet=);

    proc import out=work.&insheet

    datafile= "filename.xls"

    dbms=excel replace;

    sheet="&insheet.$";

    getnames=YES; mixed=NO;

    scantext=YES;

    usedate=YES;

    scantime=YES;

%mend imp;

However, I need to add in "guessingrows=500;" but the macro fails every time I include it. Now the macro looks like this-

%macro impb (insheet=);

    proc import out=work.&insheet

    datafile= "filename.xls"

    dbms=excel replace;

    sheet="&insheet.$";

    getnames=YES; mixed=NO;

    guessingrows=500;

    usedate=YES;

    scantime=YES;

%mend impb;

And error messages look like this-

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

ERROR 180-322: Statement is not valid or it is used out of proper order.

I've searched around and can't figure out why the macro no longer works.  Obviously it looks like is has something to do with the order, but I have moved the guessingrows statement to no success.  Thoughts?  Thanks in advance for your help!

[RW9]

Hi,

Guessingrows is not an option for xls imports.  Have a look at this recent post on the subject: https://communities.sas.com/thread/57776

Alternatively you could write a small vba macro to save all the excel files to delimited and then read that in with a datastep, which gives you full control over the data.  I posted an example vba macro some time back, will see if I can find it.

Something like this: http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/

Just add in a line for SaveAs :filetype=...

Then run that in Excel and select your folder of Excel files.

[kellyA]

Hello

Thank you!  That explains a lot.