BookmarkSubscribeRSS Feed
sfo
Quartz | Level 8 sfo
Quartz | Level 8

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

15 REPLIES 15
Reeza
Super User

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
Quartz | Level 8 sfo
Quartz | Level 8

How can I check that?

Reeza
Super User

Run the code in my first post.

 


@sfo wrote:

How can I check that?


 

sfo
Quartz | Level 8 sfo
Quartz | Level 8

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
Super User

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
Quartz | Level 8 sfo
Quartz | Level 8

Hi Reeza,

 

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

 

Thanks

Reeza
Super User

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
Quartz | Level 8 sfo
Quartz | Level 8

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

 

Attached is the log again

Patrick
Opal | Level 21

@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
Super User Tom
Super User

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
Quartz | Level 8 sfo
Quartz | Level 8

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
Super User

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
Quartz | Level 8 sfo
Quartz | Level 8
Yea I do have. Could you let me know the solution to fix this issue then ?

Reeza
Super User

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 ?


 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 7632 views
  • 0 likes
  • 5 in conversation