---SAS/ACCESS Interface to PC Files and import Excel file that is opened

[hhchenfx]

Hi Everyone,

I run proc setinit and look like I have this ---SAS/ACCESS Interface to PC Files (see below).

My question is that with the licenses I have, is there any way I can use SAS to import an Excel or CSV file that is opened in my PC.

Thank you,

HHC

I try the codes from this thread https://communities.sas.com/t5/SAS-Programming/The-EXCEL-Engine-cannot-be-found-despite-having-SAS-A... 

I run this below code and received error :

libname myRef PCFILES path='E:\Temp file\a.xlsx';
ERROR: Unable to locate pcfserver.exe. Please install PC Files Server.
ERROR: Error in the LIBNAME statement.

119  proc setinit;
120  run;

NOTE: PROCEDURE SETINIT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Original site validation data
Current version: 9.04.01M5P091317
Site name:    '***'.
Site number:  70210317.
Expiration:   14NOV2023.
Grace Period:  45 days (ending 29DEC2023).
Warning Period: 46 days (ending 13FEB2024).
System birthday:   02NOV2022.
Operating System:   WX64_WKS.
Product expiration dates:
---Base SAS Software
  14NOV2023
---SAS/STAT
  14NOV2023
---SAS/GRAPH
  14NOV2023
---SAS/ETS
  14NOV2023
---SAS/FSP
  14NOV2023
---SAS/OR
  14NOV2023
---SAS/AF
  14NOV2023
---SAS/IML
  14NOV2023
---SAS/QC
  14NOV2023
---SAS/SHARE
  14NOV2023
---SAS/ASSIST
  14NOV2023
---SAS/CONNECT
  14NOV2023
---SAS/EIS
  14NOV2023
---SAS/GIS
  14NOV2023
---SAS/SHARE*NET
  14NOV2023
---SAS Enterprise Miner
  14NOV2023
---MDDB Server common products
  14NOV2023
---SAS Integration Technologies
  14NOV2023
---SAS/Secure 168-bit
  14NOV2023
---SAS/Secure Windows
  14NOV2023
---SAS Text Miner
  14NOV2023
---SAS/Genetics
  14NOV2023
---SAS Enterprise Guide
  14NOV2023
---SAS Bridge for ESRI
  14NOV2023
---OR OPT
  14NOV2023
---OR PRS
  14NOV2023
---OR IVS
  14NOV2023
---OR LSO
  14NOV2023
---SAS/ACCESS Interface to DB2
  14NOV2023
---SAS/ACCESS Interface to Oracle
  14NOV2023
---SAS/ACCESS Interface to SAP ASE
  14NOV2023
---SAS/ACCESS Interface to PC Files
  14NOV2023
---SAS/ACCESS Interface to ODBC
  14NOV2023
---SAS/ACCESS Interface to OLE DB
  14NOV2023
---SAS/ACCESS Interface to R/3
  14NOV2023
---SAS/ACCESS Interface to Teradata
  14NOV2023
---SAS/ACCESS Interface to Microsoft SQL Server
  14NOV2023
---SAS/ACCESS Interface to MySQL
  14NOV2023
---SAS Enterprise Miner for Desktop
  14NOV2023
---SAS/IML Studio
  14NOV2023
---SAS Workspace Server for Local Access
  14NOV2023
---SAS/ACCESS Interface to Netezza
  14NOV2023
---SAS/ACCESS Interface to Aster nCluster
  14NOV2023
---SAS/ACCESS Interface to Greenplum
  14NOV2023
---SAS/ACCESS Interface to SAP IQ
  14NOV2023
---SAS/ACCESS to Hadoop
  14NOV2023
---SAS/ACCESS to Vertica
  14NOV2023
---SAS/ACCESS to Postgres
  14NOV2023
---SAS/ACCESS to Impala
  14NOV2023
---SAS/ACCESS to Salesforce
  14NOV2023
---SAS/ACCESS to HAWQ
  14NOV2023
---SAS/ACCESS to Amazon Redshift
  14NOV2023
---High Performance Suite
  14NOV2023
---SAS Add-in for Microsoft Excel
  14NOV2023
---SAS/ACCESS to SAP HANA
  14NOV2023
---SAS/ACCESS Interface to the PI System
  14NOV2023
---prod 1304
  14NOV2023
---prod 1312
  14NOV2023

[Tom]

If you want to use the PCFILES engine to allow SAS running else where to reach out to your PC and read files off of your PC's drives then you need to have the PC FILES server running on your PC.

[SASKiwi]

I'm pretty sure that using the PCFILES LIBNAME engine will not allow you to circumvent being locked out from reading an Excel file that is already open exactly as what happens with the EXCEL LIBNAME engine. Feel free to post the result if it is different.

[ballardw · Accepted solution]

Please describe the reason that you must "import" an Excel file that is open. Why is it critical? Who has the file open, you or someone else?

[hhchenfx]

Oops,

I missed clicked the Solution button 😕

Anyway, I open Excel file in my PC that has SAS running in loop every minute. I update criteria on the Excel file and want SAS read it to incorporate the new criteria into the loop code.

Thanks,

HHC

[SASKiwi]

Can you explain your SAS process please in more detail, including the criteria being entered. Trying to do this in an open Excel spreadsheet simply won't work as your local Excel app has no multi-user capabilities. You are going to have to choose another way of meeting your requirements.

[hhchenfx]

Hi,

Basically, my SAS is running few time in a minute to collect and analyze data on:

ID

price 

On the other hand, I have an Excel file opened and I suppose to update:

Target ID

Target price.

My plan is that, SAS will import the Excel file (which is opened) and for the target ID, it will check if price reach target price.

Clearly, I can update the Excel file and close it and reopen it to add new information but I rather not doing so if SAS can read opened Excel file.

Thank you.

HHC

[ballardw]

Are you entering data in the Excel sheet? What is the actual source of the data that you want to import?

This sounds like trying to kludge around a proper connection to a data source.

[Kurt_Bremser]

Instead of complicating your life through the use of Excel, write your report to a HTML file which has a reload set through a META tag, so that the browser will continuously reload it.

[hhchenfx]

Hi Kurt_Bremser,

Can you elaborate it? 

Clearly, I manually type in the ID and target price and it doesn't matter if it is in Excel or not, as long as SAS can read it while the file is opened!

Thanks,

HHC

[Kurt_Bremser]

I misinterpreted your original post. Try to save the data from Excel to a csv file and read that with a DATA step in SAS.

In a client/server environment, I would use stored processes for updating data; in these, the target dataset is blocked only for a short time. Data entry windows will usually lock the files for as long as no dedicated save or close operation has been done.

[hhchenfx]

Thanks Everyone for helping.

While I am not follow this path, the information is useful for future work.

Right now, I do a easy way by getting python to do the import for me, then export to csv. Then SAS can read this csv into SAS.

HHC

[SASKiwi]

@hhchenfx - What SAS user interface are you using? I'm assuming you are using SAS installed on your PC and possibly the SAS Windowing Environment, not Enterprise Guide? If you are using the SAS Windowing Environment then you could use the SAS WINDOW statement to enter your data rather than Excel.