topic Re: how to get worksheet names from excel using sas in SAS Programming

how to get worksheet names from excel using sas

rajesh_k_jha_accenture_com — Tue, 29 Mar 2016 13:44:35 GMT

Hi Team,

I want to get worksheet names from excel in order to validate the available sheets? since sometimes i did not get the required worksheets as part of excel and my sas codes starts throwing error for the same with proc import.

I tried below code for finding out the worksheet names available:

libname xls excel 'C:\test\testfile.xlsx';

data t;
set sashelp.vtable;
where libname = 'XLS';
run;

But continiously getting below error:

104 libname xls excel
104! 'C:\test\testfile.xlsx.xlsx';
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
105 data t;
106 set sashelp.vtable;
107 where libname = 'XLS';
108 run;

Can anyone help me on the same to resolve this error as I am stuck here!!

Re: how to get worksheet names from excel using sas

RW9 — Tue, 29 Mar 2016 14:01:39 GMT

Why not solve the "problem" whch is that your using Excel. I would assum ethat you also don't have an import agreement - i.e. a document which details the strcuture and possibly contents of the data, which both parties have agreed to? If not, whatever you do is going to be hard to maintain, validate, use further. Simply put, Excel is not a good medium for "data".

Now there are no SAS functions to check Excel properties. you could use (version 9.4) libname excel. This can create a library reference to an XLSX file, and datasets witihin that library for each sheet. You could then take the information from sashelp.vtables where library=that libref.

However, this is all just trying to plaster up a poor data transfer process, and it will never work well.

As for your code:

libname xls excel 's:\temp\rob\a.xlsx';
data want;
  set sashelp.vtable;
  where libname="XLS";
run;
libname xls clear;

The above worked fine for me. Are you using Sas 9.4? Is the file your accessing an actual XLSX file - not what the extension is called, I mean is it actually saved out of Excel as their native file, i.e. its not an XML file, or CSV or something else which can be processed by Excel. If the above are fine, make sure you have PC files access lilcensed, you also don't mention what operating system you are on.

Re: how to get worksheet names from excel using sas

Kurt_Bremser — Tue, 29 Mar 2016 14:05:53 GMT

Your error comes from either:

- you don't have SAS/ACCESS to PC Files licensed

- SAS/ACCESS was not correctly installed

- your SAS is not installed on Windows

- you have a bitness (64/32) problem between SAS and the installed MS Office

You might try to use the XLSX engine.

A much better solution is to switch from dungheap shuffling (aka "working with Excel") to transfer your data in a data transfer format like .CSV, fixed column text files, or XML.

Re: how to get worksheet names from excel using sas

rajesh_k_jha_accenture_com — Tue, 29 Mar 2016 18:24:03 GMT

Hi RW9,

Thanks for your quick help.

The file which i am trying to open is valid xlsx. I mean with valid xlsx extension.

I am currently using SAS 9.3 and i have SAS/ACCESS Interface to PC Files installed as copy of Proc setinit from my SAS PC as below:

Product expiration dates:

---Base SAS Software
31AUG2016
---SAS/STAT
31AUG2016
---SAS/GRAPH
31AUG2016
---SAS/ETS
31AUG2016
---SAS Enterprise Miner
31AUG2016
---SAS Text Miner
31AUG2016
---SAS Enterprise Guide
31AUG2016
---SAS/ACCESS Interface to PC Files
31AUG2016
---SAS Enterprise Miner for Desktop
31AUG2016
---SAS Workspace Server for Local Access
31AUG2016
---SAS Add-in for Microsoft Excel
31AUG2016

Regards,

Rajesh

Re: how to get worksheet names from excel using sas

rajesh_k_jha_accenture_com — Tue, 29 Mar 2016 18:44:58 GMT

Hi KurtBremser,

Thanks for your quick help.

The file which i am trying to open is valid xlsx. I mean with valid xlsx extension.

I tried with XLSX engine but got below error as:

libname xls xlsx "C:\test\testfile.xlsx";
ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.

I am currently using SAS 9.3 and i have SAS/ACCESS Interface to PC Files installed as copy of Proc setinit from my SAS PC as below:

Product expiration dates:

Regards,

Rajesh

Re: how to get worksheet names from excel using sas

RW9 — Tue, 29 Mar 2016 19:19:32 GMT

Yes, libname excel is 9.4 only. You could have bitness problems, are you running on Windows? I still suspect the file isn't an XLSX file - right click on it and open in text pad, if it looks like XML then you don't have a valid XLSX file. A valid XLSX file is actually a ZIP file, you can change the .XLSX to .ZIP and then double click on it, you will see subdirectories and .XML files within.

Anyways, without knowing OS/BItness, why not do this the other way round. If your fixed on using Excel then just push out the information to a usable format from there. Excel has VBA with which you can write code - doesn't necessarily have to be in your data file - youo could have your own excel file with functions and run from there. Its a very simple task from there to write a text file with sheet names, or any metadata really:

Sub WriteNames()
    Open "c:\Sheets.txt" For Output As #1
    For i = 1 To ActiveWorkbook.Sheets.Count
        Write #1, ActiveWorkbook.Sheets(i).Name
    Next i
    Close #1
End Sub

This assumes the activesheet is the one to export sheet names from.

Re: how to get worksheet names from excel using sas

LinusH — Tue, 29 Mar 2016 19:27:25 GMT

For 9.3 XKSX is not a supported engine, EXCEL us.
It supports Excel up to 2010. What version do you have, what version was the file created in?
And again, check the bits (SAS vs Office).
The libname is assigned in a Windows SAS session, right?

Re: how to get worksheet names from excel using sas

rajesh_k_jha_accenture_com — Tue, 29 Mar 2016 19:49:53 GMT

I have Office 2013 and Yes, Libname is assigned in Window SAS .

Regarding, Checking the bits (SAS vs Office), what steps do i need to follow. Could you please guide me

Re: how to get worksheet names from excel using sas

Reeza — Wed, 30 Mar 2016 02:51:10 GMT

Its likely SAS is 64bit and Excel is 32 bit.

Try Using excelcs instead of excel. Or PCFiles.

Libname xls pcfiles path="path to excelfile";

Or

Libname xls ExcelCS "path to excel file";

Re: how to get worksheet names from excel using sas

Reeza — Wed, 30 Mar 2016 02:54:42 GMT

Since it looks like you have a desktop higher end license I would also suggest upgrading since its free. Contact your SAS contact for info on how to upgrade.

Re: how to get worksheet names from excel using sas

Kurt_Bremser — Wed, 30 Mar 2016 05:31:57 GMT

Why, for $DEITY's sake, go through all the hassles?

Save the sheets as distinct .csv files, and import those. This is completely independent from SAS licensing and will work across ALL platforms/environments.

Excel native formats are no valid data transfer tool!

Re: how to get worksheet names from excel using sas

rajesh_k_jha_accenture_com — Wed, 30 Mar 2016 05:57:24 GMT

HI Reeza,

Thanks for your much needed help. Really appreciated.

So, it worked with your library decalration given as below:

Libname xls pcfiles path="path to excelfile";

I utilized them in my code as below :

libname xls pcfiles path= "C:\test\testfile.xlsx";

data t;

set sashelp.vtable;

where libname = 'XLS';

run;

Regards,

Rajesh