2
With the support of the new data types comes the ability to create datasets within a RDMNS, which is
also compliant with ANSI 1999 standards, using PROC FEDSQL (The SAS Institute, 2018).
More benefits of PROC FEDSQL
Along with the ANSI compliance and all the associated benefits that come along with it, PROC FEDSQL
brought many other benefits to SAS users. PROC FEDSQL enables the user to access multiple data
sources all within one query, as opposed to a different query for each new data source that PROC SQL
would do previously. Multiple data sources are combined in a query in PROC FEDSQL when one or more
SELECT statements are used to produce a dataset. If multiple SELECT statements are used in a query
then their resulting sets must be combined in some way using set operators so the query produces a
single output set as a result. Another benefit that comes with PROC FEDSQL is a SQL pass-through
process where the query response time is reduced and the security is enhanced. There are two types of
pass-through that PROC FEDSQL can perform, explicit and implicit pass-through. Explicit pass-through
has the user connect to a data source and then send that data source SQL statements directly for that
data source to execute. An associated benefit of this is that the user is able to use the syntax that is
native to that data source even if that syntax is non-ANSI standard. An implicit pass-through, on the other
hand, takes SAS syntax and translates it into equivalent code specific to the data source the user is
connected to, therefore, the data can be passed to the data source directly for processing. Due to the fact
that the data source is processing the query, the required data does not need to be transferred to a SAS
server, only the resulting dataset needs to be transferred, which greatly reduces query time (The SAS
institute, 2018). Since the query is being processed within the data source and only the resulting table is
being transferred back, this eliminates the need of having to transfer over tables that may contain
sensitive information. The SQL Procedure also has this capability, however, by default, the FEDSQL
Procedure attempts to use an implicit-pass through for all SQL data sources.
DIFFERENCES BETWEEN PROC SQL AND FEDSQL
Many SAS users may not be familiar with PROC FEDSQL, but with the promise of better performance
one might expect that they can go to any of their previously written PROC SQL code and then add “FED”
to the top and rerun it for instant benefits. The example code below is straight from the Base SAS 9.2
Procedures Guide. All the code is doing is creating a table in Proc SQL and then inserting data into it.
Now we copied the previous PROC SQL code and added “FED” to the beginning so we can see if it does
exactly what PROC SQL did just better.
Proc sql;
create table proclib.paylist
(IdNum char(4),
Gender char(1),
Jobcode char(3),
Salary num,
Birth num informat=date7.
format=date7.,
Hired num informat=date7.
format=date7.);
insert into proclib.paylist
values('1639','F','TA1',42260,'26JUN70'd,'28JAN91'd)
values('1065','M','ME3',38090,'26JAN54'd,'07JAN92'd)
values('1400','M','ME1',29769.'05NOV67'd,'16OCT90'd)
values('1561','M',null,36514,'30NOV63'd,'07OCT87'd)
values('1221','F','FA3',.,'22SEP63'd,'04OCT94'd);
quit;