SAS/SHARE User's Guide

SQL Programming Considerations

The REMOTE engine supports the SQL procedure Pass-Through Facility (RSPT), which allows you to pass SQL statements to a remote SAS SQL processor or DBMS through a SAS/SHARE server or a SAS/CONNECT remote host.

You can use RSPT to reduce network traffic and to shift CPU load by sending queries for remote data to a remote server.

Note: If the server is a SAS/CONNECT remote host, you can also remotely submit queries with the RSUBMIT statement to achieve the same goals. [cautend]

For example, if you specify

select emptitle as title, avg(empyears), freq(empnum)
        from sql.employee
        group by title
        order by title;

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

But, if you specify

select * from connection to remote
        (select emptitle as title, 
             avg(empyears), freq(empnum)
             from sql.employee
             group by title
             order by title);

the query is passed through the SAS/SHARE server to the SAS SQL processor, which processes each row in 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=mvs.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/SHARE 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 remote host, 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/SHARE server to the remote ORACLE server.

Chapter Contents
Previous
Next
Top of Page