DATA Step, Macro, Functions and more

Import data from XLS in SAS OnDemand for Academics

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Import data from XLS in SAS OnDemand for Academics

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 Smiley Happy


Accepted Solutions
Solution
‎07-24-2016 03:00 PM
Contributor
Posts: 24

Re: Import data from XLS in SAS OnDemand for Academics

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! Smiley Happy

View solution in original post


All Replies
Super User
Posts: 19,870

Re: Import data from XLS in SAS OnDemand for Academics

Add the keyword excel between libname and path?

 

Does Proc Import work?

Contributor
Posts: 24

Re: Import data from XLS in SAS OnDemand for Academics

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 Smiley Happy

Super User
Posts: 19,870

Re: Import data from XLS in SAS OnDemand for Academics

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

 

SAS Super FREQ
Posts: 8,868

Re: Import data from XLS in SAS OnDemand for Academics

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
Contributor
Posts: 24

Re: Import data from XLS in SAS OnDemand for Academics

Posted in reply to 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 Smiley Happy

 

Super User
Posts: 19,870

Re: Import data from XLS in SAS OnDemand for Academics

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.

Solution
‎07-24-2016 03:00 PM
Contributor
Posts: 24

Re: Import data from XLS in SAS OnDemand for Academics

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! Smiley Happy

Super User
Posts: 19,870

Re: Import data from XLS in SAS OnDemand for Academics

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

 

data work.Cars;
	set myXLS.Class;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 399 views
  • 2 likes
  • 3 in conversation