BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
bentleyj1
Quartz | Level 8

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

6 REPLIES 6
art297
Opal | Level 21

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.

bentleyj1
Quartz | Level 8

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

Mirisage
Obsidian | Level 7

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

bentleyj1
Quartz | Level 8

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.

Mirisage
Obsidian | Level 7

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

art297
Opal | Level 21

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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