topic Re: Import data from XLS in SAS OnDemand for Academics in SAS Programming

Import data from XLS in SAS OnDemand for Academics

_SAS_ — Sat, 23 Jul 2016 13:35:35 GMT

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 🙂

Re: Import data from XLS in SAS OnDemand for Academics

Reeza — Sat, 23 Jul 2016 13:37:24 GMT

Add the keyword excel between libname and path?

Does Proc Import work?

Re: Import data from XLS in SAS OnDemand for Academics

_SAS_ — Sat, 23 Jul 2016 14:01:33 GMT

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 🙂

Re: Import data from XLS in SAS OnDemand for Academics

Reeza — Sat, 23 Jul 2016 14:38:05 GMT

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

Re: Import data from XLS in SAS OnDemand for Academics

Cynthia_sas — Sat, 23 Jul 2016 19:40:22 GMT

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

Re: Import data from XLS in SAS OnDemand for Academics

_SAS_ — Sun, 24 Jul 2016 18:30:47 GMT

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 🙂

Re: Import data from XLS in SAS OnDemand for Academics

Reeza — Sun, 24 Jul 2016 18:37:03 GMT

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.

Re: Import data from XLS in SAS OnDemand for Academics

_SAS_ — Sun, 24 Jul 2016 18:59:55 GMT

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! 🙂

Re: Import data from XLS in SAS OnDemand for Academics

Reeza — Sun, 24 Jul 2016 19:35:38 GMT

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

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