topic importing Excel file through libname in SAS Programming

importing Excel file through libname

Swati24 — Thu, 06 Jun 2019 09:37:04 GMT

Hi,

I'm trying to get an excel file in my SAS with the help of libname but it is giving error to me. I'm writing a sample code for reference.

libname A '/folders/myshortcuts/SAS program/Passenger.xls';

I'm using SAS studio.

Please help me out in this

Re: importing Excel file through libname

Jagadishkatam — Thu, 06 Jun 2019 09:41:17 GMT

Could you please give us more details which dataset you have used.

The approach of generating the excel file is incorrect in your case.

you can use the proc export procedure to generate the excel file.

an example is below

proc export 
  data=sashelp.prdsale 
  dbms=xlsx 
  outfile="c:\temp\prdsale.xlsx" 
  replace;
run;

source: https://blogs.sas.com/content/sasdummy/2012/01/25/export-to-excel-got-easier/

Re: importing Excel file through libname

Swati24 — Thu, 06 Jun 2019 09:43:32 GMT

Thanks but the file which I have to import to my SAS Studio is passenger.xls. Proc Import is working fine but when I'm trying to use Libname statement it is giving error.

Re: importing Excel file through libname

Jagadishkatam — Thu, 06 Jun 2019 09:46:24 GMT

So you are trying to import via libname statement, then please try the below

/* because Excel field names often have spaces */
options validvarname=any;
 
libname xl XLSX '/folders/myfolders/sample.xlsx';
 
/* read in one of the tables */
data sample;
  set xl.sample;
run;
 
libname xl CLEAR;

Re: importing Excel file through libname

Swati24 — Thu, 06 Jun 2019 09:48:04 GMT

Thanks

I tried but it is giving me error that xls engine not found

Re: importing Excel file through libname

Swati24 — Thu, 06 Jun 2019 09:50:04 GMT

I'm using SAS Studio noy SAS 9.4 or 9.3

Re: importing Excel file through libname

Kurt_Bremser — Thu, 06 Jun 2019 10:10:57 GMT

Are you using SAS Studio from University Edition? If not, what platform is your SAS server running on?

Some DBMS= values are only available on Windows.

Re: importing Excel file through libname

Swati24 — Thu, 06 Jun 2019 10:19:46 GMT

Yes I'm using SAS Studio university Edition

Re: importing Excel file through libname

Kurt_Bremser — Thu, 06 Jun 2019 10:31:06 GMT

@Swati24 wrote:

Yes I'm using SAS Studio university Edition

In that case, you need to convert your Excel workbook to the xlsx format, as the Engines for the older xls files are only available on Windows (University Edition runs on a Linux virtual machine).

Re: importing Excel file through libname

Swati24 — Thu, 06 Jun 2019 10:32:02 GMT

Ok Thanks for the solution