topic Re: The EXCEL Engine cannot be found, despite having SAS/ACCESS Interface to PC Files in SAS Programming

The EXCEL Engine cannot be found, despite having SAS/ACCESS Interface to PC Files

wowJH — Thu, 02 May 2019 18:41:00 GMT

Hello all,

I'm new to SAS, using version 7.15 of Enterprise Guide, and have been tasked with exporting data to a specified range on an existing Excel sheet. I read that if you have SAS/ACCESS Interface to PC Files you can assign a libname that refers to the workbook you want to export to, then you can refer to any named ranges within that workbook.

I ran a proc setinit and verified that I do have SAS/ACCESS Interface to PC Files, so I tried writing a libname statement that looks like this:

libname WrkBk EXCEL "Filepath/Workbook.xlsx";

Where Filepath/Workbook.xlsx were replaced with the actual file path and workbook name.

This gives me the following two errors:

ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.

I've also tried using ODS and the XLSX engine, however neither of those can use named ranges. Any and all thoughts on how to resolve this issue are much appreciated.

Best Wishes,

Re: The EXCEL Engine cannot be found, despite having SAS/ACCESS Interface to PC Files

Reeza — Thu, 02 May 2019 20:10:40 GMT

EXCEL is the old libname method, I believe that's still for xls files.

To use PCFILES, you need to use the PCFILES keyword.

libname myRef PCFILES path='path to excel file';

XLSX is the other common approach, but I don't think that supports writing to ranges.

libname myNewRef xlsx 'path to xlsx file';

If the PCFILES doesn't work, post back with the log.

Did you confirm what was licensed vs installed as well?

@wowJH wrote:

Hello all,

I'm new to SAS, using version 7.15 of Enterprise Guide, and have been tasked with exporting data to a specified range on an existing Excel sheet. I read that if you have SAS/ACCESS Interface to PC Files you can assign a libname that refers to the workbook you want to export to, then you can refer to any named ranges within that workbook.

I ran a proc setinit and verified that I do have SAS/ACCESS Interface to PC Files, so I tried writing a libname statement that looks like this:
libname WrkBk EXCEL "Filepath/Workbook.xlsx";
Where Filepath/Workbook.xlsx were replaced with the actual file path and workbook name.

This gives me the following two errors:

ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.

I've also tried using ODS and the XLSX engine, however neither of those can use named ranges. Any and all thoughts on how to resolve this issue are much appreciated.

Best Wishes,

JH

Re: The EXCEL Engine cannot be found, despite having SAS/ACCESS Interface to PC Files

Tom — Thu, 02 May 2019 21:07:12 GMT

Are you running SAS on Unix? You cannot use EXCEL engine on Unix since there is no Unix version of Excel for it to connect to.

You will need to either use PCFILES engine to connect to SAS PCFILES server running on a Windows machine.

Or just use the XLSX engine instead.

But it might not let you have all of the same functionality.

Re: The EXCEL Engine cannot be found, despite having SAS/ACCESS Interface to PC Files

ballardw — Thu, 02 May 2019 21:08:43 GMT

@wowJH wrote:

Hello all,

I'm new to SAS, using version 7.15 of Enterprise Guide, and have been tasked with exporting data to a specified range on an existing Excel sheet. I read that if you have SAS/ACCESS Interface to PC Files you can assign a libname that refers to the workbook you want to export to, then you can refer to any named ranges within that workbook.

I ran a proc setinit and verified that I do have SAS/ACCESS Interface to PC Files, so I tried writing a libname statement that looks like this:

Proc Setinit reports on license status.

proc product_status;run;

to see if the module was actually installed.

If the log doesn't show something similar to

For SAS/ACCESS Interface to PC Files ...

then the interface to PC Files wasn't installed.

Re: The EXCEL Engine cannot be found, despite having SAS/ACCESS Interface to PC Files

wowJH — Fri, 03 May 2019 13:49:05 GMT

Okay, first of all thanks to everyone who replied all of your suggestions were helpful. I talked to a coworker and we do run SAS on a UNIX server which must be why the EXCEL engine is not working. I did get an error when using:

libname myRef PCFILES path='path to excel file';

my coworker suggested adding:

SERVER = &_clientmachine.;

and that resolved the errors and I was able to export to a named range.

However, now my issue is that SAS includes the variables names as the first row in the exported range. Here's the form of the code I'm using:

/*Define libname to output workbook*/
libname WrkBk PCFILES path="path to excel file" SERVER=&_clientmachine.;

/*Clear existing data from desired named range*/
proc datasets lib=WrkBk nolist;
	delete namedRange;
quit;

/*Export data to Excel*/
data WrkBk.namedRange;
	set importDataSet;
run;
libname WrkBk clear;

From what I've read there isn't an option to not include variable names using a data step and using putnames = no in a proc export statement doesn't seem to be able to do what I want. I know this macro: http://www.sascommunity.org/wiki/Excelling_to_Another_Level_with_SAS would do what I want, but I haven't had any success running it in Enterprise Guide.

It appears my options are as follows:

The quick and dirty option: hide the first row of the named range and pretend like it doesn't exist.
Use ODS to recreate the template.
Use SAS to export to a text file and VBA to import that into the template.

Any other suggestions or corrections would be much appreciated.

Re: The EXCEL Engine cannot be found, despite having SAS/ACCESS Interface to PC Files

Reeza — Fri, 03 May 2019 14:37:50 GMT

Redesign your Excel work book. I export my data to a different sheet and have those cells linked from the export to show only what I want.