SQL Pass Through to Excel

Accepted Solution Solved
Reply
Contributor
Posts: 39
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
Esteemed Advisor
Posts: 5,202

Re: SQL Pass Through Facility

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
Esteemed Advisor
Esteemed Advisor
Posts: 7,251

Re: SQL Pass Through Facility

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: 39

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
Esteemed Advisor
Posts: 5,202

Re: SQL Pass Through Facility

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: 39

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?

 

Esteemed Advisor
Posts: 5,202

Re: SQL Pass Through Facility

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
Esteemed Advisor
Posts: 5,202

Re: SQL Pass Through Facility

Changed the title to include the key word Excel.

Data never sleeps
Contributor
Posts: 39

Re: SQL Pass Through Facility

Thanks for your help! 

☑ This topic is SOLVED.

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

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