BookmarkSubscribeRSS Feed
Bunta
Calcite | Level 5

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

6 REPLIES 6
fred_major
Calcite | Level 5

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

Bunta
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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
TomKari
Onyx | Level 15

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

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
esjackso
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2430 views
  • 3 likes
  • 6 in conversation