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

Hi all,

 

I am new to SAS and I am trying to import an XLS in SAS and I can't seem to figure it out ...

 

I am using the following statement:

 

libname myXLS '/home/path_to_file/my_content/Class_Cars.xls';

 

and I get the following error message:

 

ERROR: Library MYXLS is not in a valid format for access method RANDOM.
ERROR: Error in the LIBNAME statement.

 

Could you please help me out?


Thanks 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
_SAS_
Obsidian | Level 7

I found it out based on navigating the library. I should have used the code without "$". Now it works.

 

data work.Cars;
	set myXLS."Class"n;
run;

Thank you for all your help! 🙂

View solution in original post

8 REPLIES 8
Reeza
Super User

Add the keyword excel between libname and path?

 

Does Proc Import work?

_SAS_
Obsidian | Level 7

Hi and thanks for the quick reply.

 

If I use 

 

libname myXLS excel '/home/path_to_file/my_content/Class_Cars.xls';

I get the following error message 

 

ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.

 

If I use 

proc import 
			out=work.Class_Cars
			datafile='/home/path_to_file/my_content/Class_Cars.xls'
			dbms=excel replace;

I get the error

 

ERROR: DBMS type EXCEL not valid for import.

 

Do you know if we are allowed to work with XLS in the SAS OnDemand for Academics?

 

Thank you 🙂

Reeza
Super User

Can you save it as XLSX and then use DBMS = XLSX

 

Cynthia_sas
SAS Super FREQ
With SAS OnDemand for Academics, I am not sure that the LIBNAME engine method will work or DBMS=EXCEL. I think the only 2 possibilities are PROC IMPORT/EXPORT with DBMS=XLS or XLSX.

cynthia
_SAS_
Obsidian | Level 7

Dear Reeza and Cynthia,

 

Thank you very much for your help.

 

The solution you proposed worked perfectly so I am posting it here for others that might need it.

 

proc import 
			out=work.Class_Cars
			datafile='/home/path_to_file/my_content/Class_Cars.xlsx'
			dbms=xlsx replace;
	sheet='Class';
run;

However you gave me another idea with the libname so I tried the following libname statement that actually worked.

 

libname myXLS xlsx '/home/path_to_file/my_content/Class_Cars.xlsx';

data work.Cars;
	set myXLS."Class$"n;
run;

The strange thing is that when I run the code, the libname is assigned but the 2nd clause gives me the error that 

 

ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File MYXLS.'Class$'n.DATA does not exist.

Do you have any idea why?

 

Thanks 🙂

 

Reeza
Super User

What do you see when you navigate to the myxls library in the Library tab?

 

Post an image of that. 

 

But basically the name should be the same as it is when you navigate to the library.

_SAS_
Obsidian | Level 7

I found it out based on navigating the library. I should have used the code without "$". Now it works.

 

data work.Cars;
	set myXLS."Class"n;
run;

Thank you for all your help! 🙂

Reeza
Super User

This should work as well, you don't need the literal (n) specification.

 

data work.Cars;
	set myXLS.Class;
run;

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!

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
  • 8 replies
  • 2406 views
  • 2 likes
  • 3 in conversation