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
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
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
Just adding some comments:
Chose explicit pass-thru:
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).
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
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: