topic Re: Reading an Excel file without using menu-driven "File....Import Data"? in SAS Procedures

Reading an Excel file without using menu-driven "File....Import Data"?

Mirisage — Fri, 10 Aug 2012 12:50:29 GMT

Hi SAS Forum,

I need to read the attached Excel file into SAS but do not want to use the menu driven “File…..Import Data…” approach.

I have made the below code by tweaking a recent Ksharp’s code and it worked.

proc import datafile='c:\temp\post_this.xls'

out=Arrears_History1 /*sas file being created*/

dbms=xls /*because my file is a CSV*/

replace; /* in case I need to run this again*/

getnames=yes;

run;

Question:

The attached Excel file is just a very small sample of my original big file. So, I may not be able to detect any errors even if they happen.

So, could any expert please let me know if this approach is correct?

Thanks

Mirisage

Re: Reading an Excel file without using menu-driven "File....Import Data"?

art297 — Fri, 10 Aug 2012 13:54:28 GMT

When you use the menu driven import data approach, near the end of the process SAS asks you if you want to save the code and, if so, where.

I have always found it useful to use the menu, select various options that appear related to whatever problem I was confronting, and then save the code so that I could use it as a template for solving similar needs.

Re: Reading an Excel file without using menu-driven "File....Import Data"?

bentleyj1 — Fri, 10 Aug 2012 15:27:59 GMT

Because you're working on the desktop, you can use a LIBNAME statement with the Excel engine to handle the workbook like it was a SAS data set. Because you're concerned about bad data, you could run a proc freq, means, or however you validate the data and then continue only if there are no problems. Below is one approach you might consider. (untested code)

If you're using SAS 9.1.3 the file must be xls. 9.1.3 doesn't work with Office 2007's xlsx format. You should flip thru the on-line documentation about the Excel libname enging first.

libname xls excel 'c:\temp\excel_file.xls';

** Count the number of problem records. ;

proc sql;

select count(*)

into : _num_bad_records

from xls.'sheet1$'n

where check_this_field_value > 100;

quit;

** If there are more than X number of problem records then abort. Must convert the character macro value to numeric. ;

data _null_;

if input(put("&_num_bad_records",$3.)3.) > 5 then abort;

run;

** Otherwise continue with Normal processing. ;

Re: Reading an Excel file without using menu-driven "File....Import Data"?

Mirisage — Fri, 10 Aug 2012 19:46:57 GMT

Hi Art and bentleyj1,

Thank both of you.

Actually "Importing the Excel file" is just a one component of my code that I am preparing now. This import has to be automated into the code. So, nobody is manually doing it.

I think as Art said, I can first do the menu driven "import data approach", and then get the code, and then embed that code piece into my large code, isn't it?

Thnaks

Mirisage

Re: Reading an Excel file without using menu-driven "File....Import Data"?

bentleyj1 — Fri, 10 Aug 2012 21:38:19 GMT

Myself, when I have something that runs unattended and there's a possiblity of data errors I do data validation and abort if appropriate. I also build in error checking and abort if appropriate there too. That way you don't waste time and resources processing crap. When you said you were concerned about detecting errors, I thought that's what you were talking about.

Sure just capture the proc import code and plug it in. Lots of people do it that way. But the Excel libname engine gives you much greater flexibility.

Re: Reading an Excel file without using menu-driven "File....Import Data"?

Mirisage — Sat, 11 Aug 2012 13:23:10 GMT

Hi Art and bentleyj1,

Thank both of you again.

Both of your inputs are very useful.

This is the first time i heard about "Excel Libname Engine".

Warm regards

Mirisage

Re: Reading an Excel file without using menu-driven "File....Import Data"?

art297 — Sat, 11 Aug 2012 14:23:07 GMT

FWIW: I just added a suggestion to the SASWare ballot. Isn't it about time that proc import and export, when providing the code that was generated, also return the code that could have been run using the libname/data step method?