Help using Base SAS procedures

proc import of excel file: SAS not reading file correctly...

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

proc import of excel file: SAS not reading file correctly...

I'm trying to import an Excel file. It's rather large, the file, but I don't think that should have anything to do with my problem. Because of an error in sas 9.4 (or so i'm told) I cannot use the import wizard (under "File"), where the error message says: "Connection failed. See log for details". So instead I'm using PROC IMPORT. But when I do this the Excel file is definitely not read correctly bu sas. When using the proc contents it only shows me gibberish/not what the dataset would look like whatsoever.  Here's my code below. What could I do to make SAS read the Excel file correctly?

proc import dbms=xlsx out=Work

datafile='c:\Emilietilimport2.xlsx';

GETNAMES=YES; 

RUN;

Proc contents data=Work;

run;


Accepted Solutions
Solution
‎10-30-2013 09:31 AM
Respected Advisor
Posts: 4,173

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

Oh, I see. You're having a "bit-ness" problem and you need the right drivers. That's actually more of a Windows/MS Office than a SAS issue. One way around this could be to save your Excel as .csv and then import this csv.

View solution in original post


All Replies
Super User
Posts: 19,855

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

You haven't given us enough information to help. I'd contact Tech Support OR save the excel file as CSV and then import that.

Super User
Posts: 11,343

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

Show the actual results of contents as well. If you tried to post it, it didn't show up.

Respected Advisor
Posts: 4,173

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

If you can create a sample Excel with the first 10 or so lines of your full data. Then try and import this sample Excel. If this also doesn't work then attach it here in this forum so we can look into it.

Not sure why the import wizard doesn't work for you. Are you using SAS EG or "PC SAS".

Occasional Contributor
Posts: 16

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

Thank you all for your fine and much appreciated replies!

After doing various Things back and forth the PROC IMPORT ultimately worked, but I can't see what I did differently. So I'll still post some images and explanation below of the problems I experienced. SADLY, though, the import wizard still doesn't work which I would much prefer to use over the Proc import. As explained at the end, for Patrick, the problem seems to stem from sas... see link.

@ Reeza: I tried importing the csv file as well, but that didn't work either. Below is an image of the output (@Ballard too). And this output doesn't make sense, for example I have 50 variables, not 3....

sasexample.png

And the print of the imported ´dataset was this:

sasexample2.png

@ Patrick: I tried using only the 10 first rows but I got the same result. About the wizard I'm using PC SAS and  the link here: http://support.sas.com/kb/43/933.html explains the problem that I'm experiencing with the wizard, but the solution the sas-site suggests doesn't work so I'm told to contact sas technical support.

Solution
‎10-30-2013 09:31 AM
Respected Advisor
Posts: 4,173

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

Oh, I see. You're having a "bit-ness" problem and you need the right drivers. That's actually more of a Windows/MS Office than a SAS issue. One way around this could be to save your Excel as .csv and then import this csv.

Occasional Contributor
Posts: 16

Re: proc import of excel file: SAS not reading file correctly...

This was very helpful too. thank you Patrick, I just hope it reads the variables correctly still when it's a csv file (binomial/character/number...) .

Super User
Posts: 19,855

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

You have more control with a CSV than you would with Excel, but its more manual in turn.

Respected Advisor
Posts: 4,173

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

Add the "guessingrows" statement to your Proc Import to ensure that the result is as good as possible. Base SAS(R) 9.3 Procedures Guide, Second Edition

There is a great blog from Chris Hemedinger about all the ways you can write to an Excel. Pretty much the same applies also for reading from Excels: http://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/

Occasional Contributor
Posts: 16

Re: proc import of excel file: SAS not reading file correctly...

Thanks Patrick, good idea with the guessing rows. When it's only 8 rows as the default it has been causing me problems indeed. And thanks for the links!

Super User
Posts: 19,855

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

You need to install PC FILES Server.

I'm guessing you have 64 bit SAS talking to 32bit Office?

Occasional Contributor
Posts: 16

Re: proc import of excel file: SAS not reading file correctly...

I read the same at the page I linked to; that it could be a 32bit to a 64 bit sas problem, and now that I've checked it out it goes to show that this IS the problem - I AM running with a 32bit office package! Darn, I bought the computer with office already installed - why would they put a 32bit office package on a 64bit system!?

Alright I'll try and install the PC files server, you think that would solve the problem so to be able to use the import wizard?

Super User
Posts: 19,855

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

Actually no, mine doesn't at least Smiley Sad

I use libname engine though and that works.

Microsoft recommends 32bit Office at the moment, 64 bit is too buggy.

Occasional Contributor
Posts: 16

Re: proc import of excel file: SAS not reading file correctly...

Alright, that's good to know about the office 32 vs 64bit, thank you Smiley Happy

Super User
Posts: 11,343

Re: proc import of excel file: SAS not reading file correctly...

Posted in reply to UlrikkaRae

And possibly a language issue around unicode or other fonts.

🔒 This topic is solved and locked.

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

Discussion stats
  • 14 replies
  • 8342 views
  • 9 likes
  • 4 in conversation