Import data from XLS in SAS OnDemand for Academics

[_SAS_]

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 🙂

[Reeza]

Add the keyword excel between libname and path?

Does Proc Import work?

[_SAS_]

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]

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

[Cynthia_sas]

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_]

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]

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_ · Accepted solution]

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]

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

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