SQL is an important language for any programmer working with data. In SAS Cloud Analytic Services (CAS) you can execute SQL queries using the fedSQL.execDirect CAS action!
Welcome back to my SAS Users blog series CAS Action! - a series on fundamentals. I've broken the series into logical, consumable parts. If you'd like to start by learning a little more about what CAS Actions are, please see CAS Actions and Action Sets - a brief intro. Or if you'd like to see other topics in the series, see the overview page.
proc cas; fedSQL.execDirect / query="select Make, Model, MSRP, mean(MPG_City,MPG_Highway) as MPG_Avg from casuser.cars where Make='Toyota' order by MPG_Avg desc"; quit;
The execDirect action executes the query in the distributed CAS environment and returns the expected results.
Using a SOURCE block
While the query we executed was simple, this is not always the case. Adding a complicated query as a string can make writing the query difficult.
Instead of specifying the query as a string in the CAS procedure, use the SOURCE statement to embed text in a variable. In the following example, I'll execute the same query as before. However, this time I'll nest the query inside a SOURCE block by specifying the SOURCE statement and name the variable MPG_toyota. Then I'll add the query inside the SOURCE block and use the ENDSOURCE statement to end the block.
proc cas; source MPG_toyota; select Make, Model, MSRP, mean(MPG_City,MPG_Highway) as MPG_Avg from casuser.cars where Make='Toyota' order by MPG_Avg desc; endsource; fedSQL.execDirect / query=MPG_toyota; quit;
After the SOURCE block is complete, you can reference the variable as the value to the query parameter in the execDirect action.
And the results:
The results returned are the same, but using a SOURCE block makes the code easier to write and maintain.
In SAS Viya, FedSQL provides a scalable, threaded, high-performance way to query data and create new CAS tables from existing tables in the CAS server. In this example we saw two distinct ways to run SQL code on SAS Viya. This is only the beginning. See the resources below for more details on PROC FedSQL.