Examples Using Remote SQL Pass-Through (RSPT)

Example 2. RSPT Services: Subsetting Remote SAS Data

Four variations of the code are used to generate a PROC SQL view named SALES08, which presents sales data for fiscal year 2008. Here are the variations:

RSPT: Server Processing and Client Viewing

The data set is subsetted in the server session where summary function (SUM) is applied. Only the summary row is returned to the client session.

Processing this view is relatively fast because the data is summarized in the server session and only the resulting view is returned to the client session.

create view servlib.sales08 as
   select customer, sum(amount) as amount
   from sales
   where year=2008 and 
      salesrep='L. Peterson'
      group by customer
      order by customer;

RSPT: Client Processing and Viewing

The client uses RSPT to process server data in the client session and to create the final view in the client session.

This code creates a PROC SQL view in a SAS library in the client session, which uses RSPT to access the remote SAS data from the server session:

Note: The libref SERVLIB can be defined for the server SAS library either in the client or the server session. In this example, a LIBNAME statement is executed in the client session to access the library that is located on the server. Alternatively, you could remotely submit a LIBNAME statement to define the library in the server session. [cautionend]

libname mylib 'C:\sales';

libname servlib '/dept/sales/revenue' server=servername;

proc sql;
connect to remote 
   (server=servername);

create view mylib.sales08 as
   select * from connection to remote
      (select customer, sum(amount) as amount
      from servlib.sales
      where year=2008 and 
            salesrep='L. PETERSON'
      group by customer
      order by customer);

RSPT: Server Processing and Viewing

The client uses RSPT to process server data in the server session and to present the final view in the server session.

In the server session, you might want to create a view that can be used by many people. By modifying the previous example to include all sales representatives, the view satisfies the needs of users who are interested in the sales that are made by more than one sales representative.

This example creates a view in the server session that summarizes the data by customer for all sales representatives:

libname servlib '/dept/sales/revenue' 
   server=servername;

proc sql;
connect to remote 
   (server=servername);

execute
   (create view servlib.cust08 as 
      select customer,
      sum(amount) as amount from sales
      where year=2008
      group by customer) by remote;

RLS: Client Processing and Viewing

The client uses RLS to process server data in the client session and to create the final view in the client session.

Using RLS, you can access the server data, and then subset and summarize the data and create the final view in the client session. The disadvantage of this method is the inefficient use of network resources to access the remote data and then to process the data in the client session.

libname mylib 'C:\sales';

libname servlib '/dept/sales/revenue' 
   server=servername;

create view mylib.sales08 as
   select customer, sum(amount) as amount
      from servlib.sales
      where year=2008 and 
         salesrep='L. PETERSON'
         group by customer
         order by customer;

Top of Page