Desktop productivity for business analysts and programmers

Pass through query

Reply
New Contributor
Posts: 2

Pass through query

Hi,

Fairly new to using SAS and did some training the other day where the trainer discussed a pass through query. Did get much chance to go into detail about it as I was the only one who would likely be using it so I have a couple of questions and wuld appreciate it if someone could just say yes or no to them.

The indication I got about a pass through was that it would be possible for me to write native SQL and send that off the server where it would be processed and then returned as SAS dataset which could be manipulated. Is this correct?

If so, does that mean I can use the full range of syntax available in SQL? An example is row_number which we use a lot. I've posted a completely made up example of the query below which return the latest code and name for an ID based on when the update.

select ID, CODE, NAME from (

select ID, CODE, NAME, row_number () over (partition by ID order by CHANGEDATE desc) as ranking

from ID_Index) as data

where ranking = 1

Could I send this and have returned as a dataset? And, the big question, how would I do this. The best I can find from websearching is:

 

Proc sql;

Connect to odbc(datasrc=”CRDM2” user=uid password=pwd); (Where this is the database I'm connecting to I assume)

Create table work.latestID as

Select * from connection to odbc

(

select ID, CODE, NAME from (

select ID, CODE, NAME, row_number () over (partition by ID order by CHANGEDATE desc) as ranking

from ID_Index) as data

where ranking = 1);

Disconnect from odbc;

Quit;

Is this correct? Sorry but I'm so new to this that if I can get this right it should save a bunch of follow up questions.

Thanks

Contributor
Posts: 38

Re: Pass through query

Hi Bunta,

I use the pass through query in a lot of my programs and find it simple now, at the beginning not so much.:smileyconfused:


/*********************************************************************************/
/*           Passthrough query to get Total Orders by day                       */
/*********************************************************************************/

proc sql exec;
    connect to sqlservr as Order (server=source.sales.ca database=Source_sales);

        create table Orders_Tot as select Process_Date,rep_cd

from connection to Order(
SELECT Process_Date,rep_cd FROM MyTable)
WHERE Process_Date ge &fromdate and Process_Date le &Todate;

disconnect from Order;
  
    quit;
run;

Fred

New Contributor
Posts: 2

Re: Pass through query

Thanks Fred. It seem then once I get the permission sorted on the database, as work is a bit slow to get them done, I'll be able write something in SQL and then analyse in EG.

This is a huge bonus as I'm more skilled in SQL then SAS for getting results sets to analyse.

Thanks

Super User
Posts: 5,386

Re: Pass through query

Just adding some comments:

Chose explicit pass-thru:

  • When wish to use functionality not available within SAS/ANSI SQL
  • When a result should be returned to the SAS session

Otherwise you can be fine using SAS SQL, the SAS/ACCESS engine will then transform and convert to the target data base SQL (implicit pass-thru).

Data never sleeps
PROC Star
Posts: 1,146

Re: Pass through query

I agree completely with 's comments. A couple of additional thoughts:

You don't necessarily need to return a result to SAS. I'm pretty sure I've used pass-through to run database utility functions against my database (I don't currently have a DBMS environment available).

Another nice use of pass-through is if you're doing a major summarization, and your database is much faster than retrieving the data to SAS and using the SAS functions. For example, on a 300 million row SQL table, to use PROC MEANS to sum income by state will read 300 million records from the DBMS into SAS, and them PROC MEANS will boil them down to 50 result rows. On the other hand, using pass-through will get the DBMS to boil it down, and only return the 50 rows to SAS. However, in many cases SAS is smart enough to interpret regular PROC SQL code and pass efficient requests through to the database. So it all comes down to cases.

Tom

Community Manager
Posts: 2,889

Re: Pass through query

FYI, PROC MEANS (and some other SAS Base procs) do "push" processing to the database where possible.  You do have to make sure that your code doesn't do anything to scuttle this effort though, such as running PROC SORT first or passing an SQL view into the proc.

This is why you might notice that EG tasks now generate slightly different syntax when running against database tables (which don't need to be sorted for BY processing) versus native SAS data sets (which do need to be sorted when using BY group processing).

See:

Getting out of SORTs with SAS data - The SAS Dummy

and

Base SAS(R) 9.2 Procedures Guide

Chris

Super Contributor
Posts: 333

Re: Pass through query

First pass through the code looks fine. Have you tried it? Are you getting errors?

You are right that the benefit / bane of passthrough sql is the ability to write SQL native to the database source syntax. Great if you dont need a SAS specific function to pull the data.

EJ

PS -- couldnt tell if from the code / post if the odbc connection was already been used ie ODBC is created and there is a license for SAS/ACCESS interface for ODBC.

Ask a Question
Discussion stats
  • 6 replies
  • 875 views
  • 3 likes
  • 6 in conversation