You can use RSPT to reduce network traffic
and to shift CPU load by sending queries for remote data to a server
session. (If the server is a
SAS/CONNECT single-user server you can
also RSUBMIT queries to achieve the same goals.)
For example, this code
contains the libref SQL that points to a server library that is accessed
through a
SAS/CONNECT or a
SAS/SHARE server. Each row in the table
EMPLOYEE must be returned to the client session in order for the summary
functions AVG() and FREQ() to be applied to them.
select employee_title as title, avg(employee_years),
freq(employee_id)
from sql.employee
group by title
order by title;However, this code contains
a query that is passed through the SAS server to the SAS SQL processor,
which processes each row of the table and returns only the summary
rows to the client session.
select * from connection to remote
(select employee_title as title,
avg(employee_years),
freq(employee_id)
from sql.employee
group by title
order by title);You can also use RSPT
to join server data with client data. For example, you can specify
a subquery against the DB2 data that is sent through the SAS server
to the DB2 server. The rows for the divisions in the southeast region
are returned to your client session, where they are joined with the
corresponding rows from the local data set MYLIB.SALES08.
libname mylib 'c:\sales';
proc sql;
connect to remote
(server=tso.shr1 dbms=db2
dbmsarg=(ssid=db2p));
select * from mylib.sales08,
connection to remote
(select qtr, division,
sales, pct
from revenue.all08
where region='Southeast')
where sales08.div=division;
If your server is a
SAS/CONNECT single-user server, you can also use RSPT to send non-query
SQL statements to a remote DBMS. For example, this code sends the
SQL DELETE statement through the SAS server to the remote Oracle server.
proc sql;
connect to remote
(server=sunserv dbms=oracle dbmsarg=(user=scott password=tiger);
execute (delete from parts.inventory
where part_bin_number='093A6')
by remote;