Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

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


Accepted Solutions
Solution
‎08-10-2012 05:38 PM
Contributor
Posts: 69

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

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.

View solution in original post


All Replies
PROC Star
Posts: 7,492

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

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.

Contributor
Posts: 69

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

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. ;

Super Contributor
Posts: 338

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

Posted in reply to bentleyj1

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

Solution
‎08-10-2012 05:38 PM
Contributor
Posts: 69

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

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.

Super Contributor
Posts: 338

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

Posted in reply to bentleyj1

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

PROC Star
Posts: 7,492

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

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?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 318 views
  • 0 likes
  • 3 in conversation