Proc import and file->import not working in 9.4

[sfo]

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

[Reeza]

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.

[sfo]

How can I check that?

[Reeza]

Run the code in my first post.

---

@sfo wrote:

How can I check that?

---

[sfo]

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.

[Reeza]

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.

---

[sfo]

Hi Reeza,

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

Thanks

[Reeza]

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.

[sfo]

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

Attached is the log again

[Patrick]

@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?

[Tom]

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.

[sfo]

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;*/

[Reeza]

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;*/

---

[sfo]

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

[Reeza]

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 ?

---