topic Re: Excel Libname creating unusable datasets in SAS Programming

Excel Libname creating unusable datasets

KRusso — Mon, 10 Oct 2016 16:59:38 GMT

Hello. I am working through the SAS Base Class and am trying to assign a library to an excel document.

When I execute the code as directed in the tutorial, everything works swimmingly.

libname orionx pcfiles path="&path/Sales.xls";
run;

Then, I tried to apply it to my own data. The code runs without error. When I open the library, I see Sheet1$, Sheet2$ and Sheet3$.

But there is not data in any of the sets. Also, I receive an error that the table Lx."Sheet1$"n either does not exist or cannot be displayed. None of the tables have any observations. Any ideas?

2009 libname lx pcfiles path="&path/TryingItOut.xlsx";
NOTE: Libref LX was successfully assigned as follows:
Engine: PCFILES
Physical Name: H:\UserData\Desktop\ecprg193\TryingItOut.xlsx
2010 run;

Re: Excel Libname creating unusable datasets

Haris — Mon, 10 Oct 2016 17:03:22 GMT

In the current build of SAS 9.4, you can simply assign a libname <Name> Excel 'c:\folder\File.xlsx';

Did you try not using PCFiles?

Re: Excel Libname creating unusable datasets

KRusso — Mon, 10 Oct 2016 17:18:35 GMT

I receive this error:

libname lx excel "&path/TryingItOut.xlsx";
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

Re: Excel Libname creating unusable datasets

Haris — Mon, 10 Oct 2016 17:22:29 GMT

Yeah, clearly, we're not working on the same versions of SAS. Give libname XLSX ''; a try...

Re: Excel Libname creating unusable datasets

Reeza — Mon, 10 Oct 2016 17:25:23 GMT

There may be something in the excel file that's causing issues. Try removing worksheets and having very simple excel sheets first.

Re: Excel Libname creating unusable datasets

KRusso — Mon, 10 Oct 2016 18:15:27 GMT

So the odd thing is that if I name the range, it works. Also, It appears that I can reference the datasets in the sheet, I just cannot view them.

Re: Excel Libname creating unusable datasets

Haris — Mon, 10 Oct 2016 18:16:45 GMT

Make sure your Excel file is not open when you're running SAS! If Excel is open, SAS can't get access to the data therein.

Re: Excel Libname creating unusable datasets

KRusso — Mon, 10 Oct 2016 18:22:51 GMT

After I've been trying it again, It appears via proc contents and proc print that the data is there. However, when I try to view in the explorer it does not display. So it's at least functional.

Re: Excel Libname creating unusable datasets

SASKiwi — Mon, 10 Oct 2016 18:29:29 GMT

If you haven't tried it try importing with: options validvarname = v7; to see if that helps.