You can connect to the database. That’s required. And then you issue your typical ANSI standard for the version of SQL for that database. If it has enhancements, you can use them. If it has beyond the ANSI standard, you can use whatever the capability is. In this case, we SELECT columns 1, 2, through n. So you put the syntax as FROM CONNECTION TO DBMS through this one here. And then, in parentheses, you can indicate your specific database SQL query. You can also execute other DBMS-specific statements by that database, and when you’re done, DISCONNECT. So that’s a pass-through. You’re passing your query directly to the database, so the syntax has to be correct. It has to be acceptable, but you still will get an error.
Let’s go back to the story once again. WEEKDAY. When we received a message, it cannot be passed. Right? Remember that? So how could we improve this? We could use data set options, to improve the retrieval, and PROC MEANS, or we can rewrite PROC MEANS in the form of PROC SQL and do an explicit pass-through. Remember we did this earlier. We went against the Oracle table Order_Fact red buff 1,000 records buffer time. The database conditions. And we already passed it in the database. And this is how you type it when looking for Saturday, but you have to know the syntax. And RHINOS to improve the likelihood of it performing a task. That’s one option. Here’s the other option. We connect to the Oracle database, put in our credentials, then select star. Now, before we finish that, go below.
Do you see in blue, this color, this aqua-green type of color? From this open parentheses to that close parentheses, we select to do a query against the database. For the customer ID, we’re getting statistics like the count min, average, max. These arguments assign to each respective variable from this table, filtering for Saturday, the group by customer ID, and we order by customer ID. So this results set, when it comes back, pass to this select and select all the columns from that results set. And by default, we get a report, and then we disconnect. All right. More on WEEKDAY in the WHERE revisited. Now, if we take a closer look at this SELECT. This pass-through has both a SAS portion and a database portion. It is possible to leverage both. So if you look at the SELECT, we’re using a format z for leading zeros. We’re specifying Nobs, the MIN, the mean, the Max. We have labels and formats. So colon outside of parentheses execution SAS. We are enhancing that results set. You can do that. These are labels. This report is nicely formatted because of the enhancements. Check out the SELECT customer_ID COUNT, MIN, and AVG, and those column names. From ORDER_FACT. Here’s the result. Oh. So sometimes to try to reduce data retrieval, SAS computes the results faster, maybe. The best thing to do, as we’ve said all along, is the benchmark.
I’m comparing the means. SAS program versus SQL to get the same summaries. And we look at our results for the benchmark map or program, a nice utility program, against the means. SAS and SQL.SAS. You look at the clock time. You look at the CPU time and the maximum amount of memory. So that’s why it’s important to still benchmark, as opposed to going by your gut feeling. If this is the program you plan to run routinely– like, as a production job, it’s something you’d run almost every other day, then this will be long-term, not just for a short period. It’s worthwhile to put the time in to evaluate the performance. So with PROC SQL, you can query SAS data sets. You can query a relational database just to run the pass-through code native to that database. But what about Fed SQL? Well, it’s new and modern, highly scalable. It’s part of base SAS, like PROC SQL. It is the base SAS implementation of Structured Query Language. Based on the 1999 standard, ANSI standard. You can process data sets to the library reference. Very ANSI compliant. So the guidelines you use for ANSI standard– SELECT, WHERE, FROM.
There are a few SAS extensions. Most queries from Fed SQL will be executed entirely in the database. All keywords are reserved words. You can do ANSI type logic that results in TRUE, FALSE, or NULL. Once again, we turn on the castrate option. We begin with the word options. Notes in the log to make it more readable. And we plug in the fulls’ timer option. Remember we talked about this earlier in the course? It gives you more performance statistics in the log about your step. And in this case, that SQL, let’s take a closer look. I want to select the Customer_ID, the Count. And I’m creating a column called a number of obs. Here’s the comma, the minimum, the average, the maximum retail price as these respective columns. From Order_Fact, which is an Oracle table, here’s WEEKDAY again looking 7, and group and order by, then return trace off. And full-timer to turn off. Fulls timer for tracking performance. Despite engaging in to castrate, we don’t have any indication that the database did any better. Both procedures, SQL and Fed SQL, both support undocumented procedure invocation option _METHOD.
You can look up more information in this way. It’s an underscore METHOD option. So if you invoke that option, you will get more information. Underscore method. You do the same task against this Oracle table. And we get information about the aggregation and sorting performed by SAS. Now if you want to guarantee the query against your table can be handled by the database, you can do an explicit pass-through to guarantee that. All right. So let’s take a look. Similar syntax to PROC SQL to some extent. Now you don’t see a connection– to connection that we saw with the PROC SQL. If you look at the slide here, you can see it points out that it makes a connection, and DISCONNECT is not required because it’s using metadata from the library to make the connection. You can do an explicit pass-through. You can also execute DBMS-specific things like granting only permission to the table. So here’s PROC SQL. We put the underscore method here, so we get more information about the method used to do the retrieval.
I want to look at what’s in the inner parentheses first because once again, this is what’s going to the database against that table. I want the Customer_ID, the Count, these statistics from the ORDER_FACT. Look on Saturday. Group and order by. Whatever you get from this will be a result set. That results set, from that, we’re selecting all the columns from that connection to Orion DB. So these lire says it’s pointing to an Oracle database that determines the platform where this would be executed. And again, you have to type native syntax for that database in these parentheses. So you use the METHOD output, and you can see in the notes and the log methods, the task was performed by the database and process.
What are some advantages of SQL versus Fed SQL for explicit SQL pass-through? The database can optimize the summarization in filtering, ordering, and joining. It can optimize the use of functions. SAS and DBMS features can be used. SAS features that can be translated. We can run DBMS stored procedures and macros. If you have authority, you can execute a DROP, a GRANT. You can DROP a table. You can GRANT authority to read a table.