What is FedSQL? It’s SAS’ proprietary implementation of the ANSI SQL:1999 core standard. It’s a vendor-agnostic SQL dialect. Therefore, one does not have to write SQL code in data source specific SQL. Most importantly, it executes in Cloud Analytic Services (CAS) whereas PROC SQL does not. In CAS, FedSQL provides a scalable, threaded, high-performance way to run queries. These queries can perform data management transformations and manipulations by querying the data in existing CAS tables and creating new CAS tables/result sets.
FedSQL statements can be submitted to the CAS server from a SAS client or SAS Viya session using the FedSQL procedure or the fedsql.execDirect CAS action. The fedsql.execDirect action can be called from a SAS Viya CASL (CASL = Cloud Analytics Services Language) program using PROC CAS or a Python, R, or Lua program.
When FedSQL statements are executed by the CAS server, the FedSQL result set can be an in-memory CAS table or the FedSQL result set can be sent back to the client without creating a CAS table. The latter situation occurs if the CAS query contains a SELECT statement without a CREATE TABLE AS statement. You can use other CAS actions to persist the result set on the CAS server or to save the result set to an external data source for further processing.
With the upcoming release of Viya 3.5 in November 2019, several new capabilities have been added to FedSQL in CAS.
data casuser.cars1 (keep= make model type origin);
set sashelp.cars;
run;
data casuser.cars2 (keep= make model type origin);
set sashelp.cars;
where origin = 'USA';
run;
proc fedsql sessref=mysess;
create table casuser.cars_union {options replace=true} as
select * from casuser.cars1
union
select * from casuser.cars2;
quit;
proc fedsql sessref=mysess;
create table casuser.cars_cast {options replace=true} as
select cast(msrp as char(8)),
cast(invoice as char(8))
from casuser.cars;
quit;
proc contents data=casuser.cars_cast; run;
These are just some of the major improvements made to FedSQL and the fedsql.execDirect action in SAS Viya 3.5. Details about additional enhancements can be found in the documentation once it’s available. These include:
A big thank you to Andy Therber for his contributions to this article.
Beautifully explained and good article. Please can you give me an example of how to use
"The fedsql.execDirect action has four new instructions/parameters for the CNTL= option."
say in this example code.
proc cas;
fedsql.execDirect/;
query=
"
select * from TABLE3 ;
"
;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.