Help using Base SAS procedures

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

Reply
New Contributor
Posts: 2

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

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!

Super User
Super User
Posts: 7,392

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

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.

New Contributor
Posts: 2

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

Hello

Thank you!  That explains a lot.

Ask a Question
Discussion stats
  • 2 replies
  • 1087 views
  • 0 likes
  • 2 in conversation