DATA Step, Macro, Functions and more

Excel Libname creating unusable datasets

Reply
Occasional Contributor
Posts: 10

Excel Libname creating unusable datasets

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;

Frequent Contributor
Posts: 102

Re: Excel Libname creating unusable datasets

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?

Occasional Contributor
Posts: 10

Re: Excel Libname creating unusable datasets

I receive this error:

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

Re: Excel Libname creating unusable datasets

Yeah, clearly, we're not working on the same versions of SAS. Give libname XLSX ''; a try...
Super User
Posts: 19,770

Re: Excel Libname creating unusable datasets

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

Occasional Contributor
Posts: 10

Re: Excel Libname creating unusable datasets

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.

Frequent Contributor
Posts: 102

Re: Excel Libname creating unusable datasets

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.
Occasional Contributor
Posts: 10

Re: Excel Libname creating unusable datasets

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.
Super User
Posts: 3,250

Re: Excel Libname creating unusable datasets

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

Ask a Question
Discussion stats
  • 8 replies
  • 501 views
  • 0 likes
  • 4 in conversation