SQL Pass Through to Excel

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

SQL Pass Through to Excel

[ Edited ]

Hi I am trying to use the pass through facility in SAS, I am running SAS 9.2 but it does not seem like I have the correct software installed to run, below are two examples and the assoicated log notes.

 

Method 1

 

 

proc sql noprint;
  connect to pcfiles (path = "C:\My Work\Documents\MyEXCEL.xls" server = market port = 1234);
    create table sasdat as
    select * from connection to pcfiles (select * from mydata);
  disconnect from pcfiles;
quit;

When I run the above code I get the following log notes.

 

 

ERROR: The PCFILES engine cannot be found.
ERROR: A Connection to the pcfiles DBMS is not currently supported, or is not installed at your site.
ERROR: Connection to the pcfiles DBMS does not exist.

 

Method 2

 

proc sql noprint; 
  connect to excel (path = "C:\My Work\Documents\MyEXCEL.xls"); 
    create table domains as 
    select domain_name, domain_label, key_variables 
    from connection to excel (select * from [sheet1$]); 
  disconnect from excel;
quit;

When I run the above code I get the following log notes.

 

ERROR: The EXCEL engine cannot be found.
ERROR: A Connection to the excel DBMS is not currently supported, or is not installed at your site.
ERROR: Connection to the excel DBMS does not exist.

 

Question

I really would like to be able to use one of these method as they are quick and clean. That said what do I need to do in order for these methods to work?

 


Accepted Solutions
Solution
‎05-10-2016 10:37 AM
Super User
Posts: 5,775

Re: SQL Pass Through Facility

Posted in reply to craig159753

Excel is supported (engine PCFILES) for SQL pass thru, even in 9.2. If it's best practice to use it or not, I leave that to be discussed in another thread...

But as the message indicates, your are lacking of licence, installation, or both.

 

What does

proc setinit;
run;

give you?

 

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002644751.htm

Data never sleeps

View solution in original post


All Replies
Super User
Super User
Posts: 8,993

Re: SQL Pass Through Facility

Posted in reply to craig159753

SQL passthrough is for passing SQL through to a compliant database using ODBC or other driver.  Excel is Not a database.  

 

You can read Excel files in via a few methods (all of which have the inherent problems of dealing with Excel files):

proc import - its a guessing procedure, so between the unstrructured/controlled format of excel and this, your data is 40% likely to be messed up, but it is easy to use.

libname excel - same as above.

Save to CSV, then write a datastep import program - this fixes the guessing imported data as you specify what the data is to be input as, but of course there is still Excel problems.

Contributor
Posts: 49

Re: SQL Pass Through Facility

When I was running SAS EG with SAS 9.4 Method 2 worked perfectly, it would effectly import the sheet I wanted to a data set.

Solution
‎05-10-2016 10:37 AM
Super User
Posts: 5,775

Re: SQL Pass Through Facility

Posted in reply to craig159753

Excel is supported (engine PCFILES) for SQL pass thru, even in 9.2. If it's best practice to use it or not, I leave that to be discussed in another thread...

But as the message indicates, your are lacking of licence, installation, or both.

 

What does

proc setinit;
run;

give you?

 

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002644751.htm

Data never sleeps
Contributor
Posts: 49

Re: SQL Pass Through Facility

I literally just found something about this and I can not see PC Files in my list. How would I go about getting this lisence?

 

Super User
Posts: 5,775

Re: SQL Pass Through Facility

Posted in reply to craig159753

So you have 9.4 with EG? Why don't you stick to that, or are you at different sites?

First, talk to the SAS admin/representative at your site. Then you/he/she contact SAS for a license extension for SAS/ACCESS to PC File Formats.

Data never sleeps
Super User
Posts: 5,775

Re: SQL Pass Through Facility

Posted in reply to craig159753

Changed the title to include the key word Excel.

Data never sleeps
Contributor
Posts: 49

Re: SQL Pass Through Facility

Thanks for your help! 

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 1135 views
  • 0 likes
  • 3 in conversation