Help using Base SAS procedures

Problem with libname excel to import xlsm file

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

Problem with libname excel to import xlsm file

[ Edited ]

Hi,

 

I'm trying to import the xlsm file using libname excel statement:

 

libname test excel "C:\...\test_file.xlsm";

The test file is very simple, it has 2 variables and 2 observations.

 

When I run the code, SAS creates the library test as expected, but I cannot open the dataset with data taht is inside. It says that the table does not exist or it cannot be displayed. Why?


Accepted Solutions
Solution
‎10-31-2017 07:13 AM
SAS Super FREQ
Posts: 356

Re: Problem with libname excel to import xlsm file

Posted in reply to error_prone

It appears that the PC Files Server isn't installed.  If it's installed then there should be an item under the SAS start menu named "PC File Server".

 

I don't think there is a bitness mismatch because the original post indicated that the library was assigned.

 

Save the attached "Book1.xlsm.txt" file on your system and then rename it to remove the .txt extension (forum software disallows XLSM files).  Modify the code below to specify the correct file path, and then run the code:

  

options validvarname=any validmemname=extend;

libname indata excel 'path-to-file\Book1.xlsm' access=read;

title 'EXCEL Engine';

proc contents data=indata._all_; run; quit;

proc print data=indata.'Sheet1$'n; run; quit;


libname indata xlsx 'path-to-file\Book1.xlsm' access=read;

title 'XLSX Engine';

proc contents data=indata._all_; run; quit;

proc print data=indata.Sheet1; run; quit;

 

 PROC CONTENTS reports these table names for the EXCEL engine:

 

INDATA.'Sheet1$'n
INDATA.'Sheet2$'n
INDATA.'Sheet3$'n

 

and these for the XLSX engine:

 

INDATA.SHEET1
INDATA.SHEET2
INDATA.SHEET3

 

You must use the names that the respective engine expects (case does not matter in this instance).

 

Does that help?

 

Vince DelGobbo

SAS R&D

View solution in original post


All Replies
Regular Contributor
Posts: 213

Re: Problem with libname excel to import xlsm file

Posted in reply to chris2377
What is the name of the sheet?
Contributor
Posts: 67

Re: Problem with libname excel to import xlsm file

Posted in reply to error_prone

@error_prone Default name ("Sheet1"). I've tried to import other files where sheets are named differently. Every time, datasets appear in the library but I cannot open them

SAS Super FREQ
Posts: 356

Re: Problem with libname excel to import xlsm file

Posted in reply to chris2377

Try the PCFILES engine:

 

libname indata pcfiles path='C:\temp\myfile.xlsm' access=read;

options validvarname=any validmemname=extend;

proc contents data=indata._all_; run; quit;

 

Vince DelGobbo

SAS R&D

Contributor
Posts: 67

Re: Problem with libname excel to import xlsm file

Posted in reply to Vince_SAS

@Vince_SAS

I 've tried. The result is: 

 

ERROR: Internal error: unable to communicate with PC Files Server process.
ERROR: Error in the LIBNAME statement

SAS Super FREQ
Posts: 356

Re: Problem with libname excel to import xlsm file

Posted in reply to chris2377

I should have asked earlier - What operating system is SAS running on, and what version of SAS are you using?

 

On UNIX systems the SAS PC Files Server needs to be started and running on a Windows machine.

 

Vince DelGobbo

SAS R&D

Contributor
Posts: 67

Re: Problem with libname excel to import xlsm file

Posted in reply to Vince_SAS

@Vince_SAS

 

Sas 9.4 M3 + Windows 7 64 bit

Regular Contributor
Posts: 213

Re: Problem with libname excel to import xlsm file

Posted in reply to chris2377

Afaik you still need the PC Files service, assuming that MS Office is 32bit.

Solution
‎10-31-2017 07:13 AM
SAS Super FREQ
Posts: 356

Re: Problem with libname excel to import xlsm file

Posted in reply to error_prone

It appears that the PC Files Server isn't installed.  If it's installed then there should be an item under the SAS start menu named "PC File Server".

 

I don't think there is a bitness mismatch because the original post indicated that the library was assigned.

 

Save the attached "Book1.xlsm.txt" file on your system and then rename it to remove the .txt extension (forum software disallows XLSM files).  Modify the code below to specify the correct file path, and then run the code:

  

options validvarname=any validmemname=extend;

libname indata excel 'path-to-file\Book1.xlsm' access=read;

title 'EXCEL Engine';

proc contents data=indata._all_; run; quit;

proc print data=indata.'Sheet1$'n; run; quit;


libname indata xlsx 'path-to-file\Book1.xlsm' access=read;

title 'XLSX Engine';

proc contents data=indata._all_; run; quit;

proc print data=indata.Sheet1; run; quit;

 

 PROC CONTENTS reports these table names for the EXCEL engine:

 

INDATA.'Sheet1$'n
INDATA.'Sheet2$'n
INDATA.'Sheet3$'n

 

and these for the XLSX engine:

 

INDATA.SHEET1
INDATA.SHEET2
INDATA.SHEET3

 

You must use the names that the respective engine expects (case does not matter in this instance).

 

Does that help?

 

Vince DelGobbo

SAS R&D

Contributor
Posts: 67

Re: Problem with libname excel to import xlsm file

Posted in reply to Vince_SAS
Thanks, it works now.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 1167 views
  • 0 likes
  • 3 in conversation