DATA Step, Macro, Functions and more

Proc import and file->import not working in 9.4

Reply
Frequent Contributor
Frequent Contributor
Posts: 92

Proc import and file->import not working in 9.4

I am trying to import xlsx file using SAS 9.4 and even  DBMS=excelcs option or DBMS=XLSX option doesn't seem to work. Infact I can't even import the file using file->import.

 

Can anyone help.

 

Thanks

Super User
Posts: 23,980

Re: Proc import and file->import not working in 9.4

Do you have the appropriate license to import an Excel file?

 

proc product_status;run;

I believe you're looking for SAS/Access to PC FILES.

Frequent Contributor
Frequent Contributor
Posts: 92

Re: Proc import and file->import not working in 9.4

How can I check that?

Super User
Posts: 23,980

Re: Proc import and file->import not working in 9.4

Run the code in my first post.

 


@sfo wrote:

How can I check that?


 

Frequent Contributor
Frequent Contributor
Posts: 92

Re: Proc import and file->import not working in 9.4

[ Edited ]

Thanks for the suggestion, below is the code and the error in the log, also I can't even import using the File->Import option

 

Code:

 

PROC IMPORT OUT= outdata
DATAFILE= "xxx\yyy.xls"
DBMS=EXCEL REPLACE;
SHEET="Sheet1";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

 

Error:
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

Super User
Posts: 23,980

Re: Proc import and file->import not working in 9.4

You should delete the attached file. It has your place of work and site no. 

 

You seem to have correct license. Please show the code/log from your proc import step. 

 


@sfo wrote:

I think so it runs. Attached is the log file.


 

Frequent Contributor
Frequent Contributor
Posts: 92

Re: Proc import and file->import not working in 9.4

Hi Reeza,

 

Do you have a solution after seeing the export code and the log?

 

Thanks

Super User
Posts: 23,980

Re: Proc import and file->import not working in 9.4

[ Edited ]

1. Try ExcelCS (if you haven't already)

2. You may need PC FILES server installed if your SAS is 64 bit and Excel is 32 bit.

 

PS. I can't recall what's in the log anymore, you should post just that portion as text directly into the forum here, not the whole log.

 

 

Frequent Contributor
Frequent Contributor
Posts: 92

Re: Proc import and file->import not working in 9.4

I tried ExcelCS and it didn't work as well.

 

Attached is the log again

Respected Advisor
Posts: 4,779

Re: Proc import and file->import not working in 9.4

@sfo

Your log shows that SAS/Access to PC files is installed. Also run a Proc Setinit to check if it's licensed as well.

If both is true (installed and licensed) then things should work with engines like XLSX.

 

You write that even a File/Import doesn't work (that's out of SAS EG?). That makes me think that eventually something with your Excel source isn't as it should.

1. Can you open this Excel with MS Excel?

2. Is it an . xlsx or something else (like an .xlsm)?

3. Has importing Excel files ever worked in your environment?

4. Can you import other Excels right now?

Super User
Super User
Posts: 8,267

Re: Proc import and file->import not working in 9.4

The error message about "CLASS" is caused by incompatibility between the "bitness" of SAS and your Microsoft Office installation.

If you are running a reasonably recent version of SAS don't use the EXCEL engine and instead use the XLSX or XLS engine. That does not make any calls to non-SAS software and so will not have any issue with compatibility.

 

PROC IMPORT OUT= outdata REPLACE
  DATAFILE= "xxx\yyy.xls" 
  DBMS=XLS 
;
RUN;

Also make sure what you have is really is an Excel file and not some other type of file that has been saved with the .XLS extension to trick your operating system into using Excel to open the file and letting Excel convert it on the fly.

 

Frequent Contributor
Frequent Contributor
Posts: 92

Re: Proc import and file->import not working in 9.4

I tried running the code again and the Import works except I had to hide three lines. Can you confirm if it will be ok if these three lines are hidden?

 

DATAFILE= "dir\file.xls"
DBMS=XLS ;
SHEET="Sheet";
GETNAMES=YES;
MIXED=NO;
/* SCANTEXT=YES;*/
/* USEDATE=YES;*/
/* SCANTIME=YES;*/

Super User
Posts: 23,980

Re: Proc import and file->import not working in 9.4

Do you have times/dates in your data?
It will be read, but possibly not how you want.

 


@sfo wrote:

I tried running the code again and the Import works except I had to hide three lines. Can you confirm if it will be ok if these three lines are hidden?

 

DATAFILE= "dir\file.xls"
DBMS=XLS ;
SHEET="Sheet";
GETNAMES=YES;
MIXED=NO;
/* SCANTEXT=YES;*/
/* USEDATE=YES;*/
/* SCANTIME=YES;*/


 

Frequent Contributor
Frequent Contributor
Posts: 92

Re: Proc import and file->import not working in 9.4

Yea I do have. Could you let me know the solution to fix this issue then ?

Super User
Posts: 23,980

Re: Proc import and file->import not working in 9.4

Check your data and do the conversions manually using PUT/INPUT. You ultimately have few options with Excel types:

 

1. Convert to CSV and have full control

2. Import as XLS or XLSX and clean up after the fact. 

 


@sfo wrote:
Yea I do have. Could you let me know the solution to fix this issue then ?


 

 

Ask a Question
Discussion stats
  • 15 replies
  • 154 views
  • 0 likes
  • 5 in conversation