SAS/CONNECT User's Guide

Compute Services and RSPT

You can use RSPT to reduce network traffic and to shift CPU load by sending queries for remote data to a remote server. (If the server is a SAS/CONNECT single-user server you can also RSUBMIT queries to achieve the same goals.) For example, if you specify

select employee_title as title, avg(employee_years), 
   freq(employee_id)
      from sql.employee
      group by title
      order by title;

where SQL is the libref for a remote SAS library accessed through a SAS/CONNECT or a SAS/SHARE server, each row of the table EMPLOYEE must be returned to your local SAS session for the summary functions AVG() and FREQ() to be applied to them.

But, if you specify

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);

the query 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 your local SAS session.

You can also use RSPT to join remote data with local data. For example, if you specify

libname mylib 'c:\sales';

proc sql;
   connect to remote 
      (server=tso.shr1 dbms=db2 
       dbmsarg=(ssid=db2p));

   select * from mylib.sales97, 
      connection to remote 
         (select qtr, division, 
                 sales, pct
            from revenue.all97
            where region='Southeast')
      where sales97.div=division;

the subquery against the DB2 data is sent through the SAS server to the DB2 server and the rows for the divisions in the southeast region are returned to your local SAS session, where they are joined with the corresponding rows from the local data set MYLIB.SALES97.

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,

proc sql; 
   connect to remote 
      (server=sunserv dbms=oracle);

   execute (delete from parts.inventory 
      where part_bin_number='093A6')
      by remote;

sends the SQL DELETE statement through the SAS server to the remote ORACLE server.

Chapter Contents
Previous
Next
Top of Page