Table-Loading Techniques :: SAS® Scalable Performance Data Server 5.3: User's Guide

Overview

This section illustrates some of the methods that are available for loading tables into SPD Server. You can load SAS data into SPD Server tables using PROC COPY, DATA step programs, PROC APPEND, and SCL applications. You can also use SQL pass-through to load the server tables. The server SQL statement extensions, LOAD TABLE and COPY TABLE, provide further support. These statements load tables from one SPD Server domain to another. You will want to use the method that best fits your needs for source location, indexing, and creating subsets of the source data.

The examples in this section load a SAS table named Cars into SPD Server. The SAS table is available from the Sashelp library, which is shipped with all SAS software. The library is available by using the libref Sashelp in your SAS requests.

Load a SAS Table with PROC COPY

PROC COPY can be used to copy the entire content of a SAS library or specified tables from a SAS library to SPD Server. This example shows how to load all the tables in the Sashelp library to SPD Server with PROC COPY. The PROC COPY statement reads the SAS tables in the Sashelp library and writes server tables to the Conversion_Area domain. By default, PROC COPY automatically re-creates any indexes. However, it supports an option to suppress index creation. For more information about the options available with PROC COPY, see COPY Procedure in the Base SAS Procedures Guide.

libname spds sasspds 'conversion_area'
server=husky.spdsname
user='siteusr1'
prompt=yes;

proc copy in=sashelp out=spds;
run;

Using the same LIBNAME statement, this example shows how to load only the Sashelp.Cars table on SPD Server with PROC COPY. You identify the table to copy with the SELECT statement.

proc copy in=sashelp out=spds;
select cars;
run;

Load a SAS Table with the DATA Step

This DATA step example creates server table Spds.Cars2 from SAS table Cars. The DATA step copies the data but does not rebuild indexes. If you want indexes, you must create them.

data spds.cars;
set sashelp.cars ;
run ;

Parallel Table Load Technique Using the DATA Step and PROC APPEND

Using the DATA step, you can create an empty table first, defining indexes on the desired columns. Then, use PROC APPEND to populate the table and indexes. The example below demonstrates the technique. This example creates server table Spds.Cars3 from SAS table Cars.

/* Create an empty server table with the same */
/* columns and column attributes as the existing */
/* SAS table. */

data spds.cars3 (index=(make origin type));
set sashelp.cars(obs=0);
run;

/* Use PROC APPEND to append the data in SAS table */
/*  Cars to server table Cars. The append to the */
/* server table and its indexes will occur in parallel. */

proc append
base=spds.cars3
data=sashelp.cars;
run;

The SPD Server I/O engine buffers rows to be added from the SAS application and performs block adds using a highly efficient pipelined append protocol when communicating with the proxy.

Load a SAS Table with Implicit SQL Pass-Through

In these PROC SQL examples, the server table Spds.Cars4 is created from SAS table Cars. Be sure to set the IP=YES LIBNAME option in the LIBNAME statement to invoke implicit SQL pass-through. Also, set the DBIDIRECTEXEC=YES system option. The first example copies the data in its entirety. Indexes are not copied. Indexes must be defined separately.

libname spds sasspds 'conversion_area'
server=husky.spdsname
user='siteusr1'
prompt=yes
ip=yes;

option dbidirectexec=yes;

proc sql;
create table spds.cars4 as 
select * from sashelp.cars;
quit;

This example uses a subset of the columns from the SAS table to create server table Spds.Cars5:

proc sql;
create table spds.cars5 as
select make, model, origin, type, msrp from sashelp.cars;
quit;

Loading Tables between Server Domains

Copy a Server Table

This example copies the SPD Server table Cars3 that was created in Parallel Table Load Technique Using the DATA Step and PROC APPEND and creates a new server table, Copycars, using the COPY TABLE statement. The COPY TABLE statement functions similarly to PROC COPY. That is, it copies the source table and its indexes in their entirety by default, but enables you to suppress index creation if you choose. You can also specify to order the copied data with a BY statement. For more information, see COPY TABLE Statement. The creation of table Copycars and its indexes occurs in parallel.

proc sql;
connect to sasspds (host="husky" 
                    service="spdsname" 
                    dbq="conversion_area" 
                    user="siteusr1" 
                    prompt=yes);
execute(
copy table copycars
  from cars3
) by sasspds;
disconnect from sasspds;
quit;

Load a Server Table

This example creates new server table Carload from server table Cars3 that was created in Parallel Table Load Technique Using the DATA Step and PROC APPEND. The LOAD TABLE statement creates a new table using content from an existing table. It can load all the data, as shown below. In addition, it supports a SELECT clause and a WHERE clause to enable you to subset the data. For an example of subsetting data with LOAD TABLE, see Load and Subset a Server Table. LOAD TABLE does not re-create indexes. If you want indexes, you must define them. The table creation occurs in parallel.

proc sql;
connect to sasspds (host="husky" 
                    service="spdsname" 
                    dbq="conversion_area" 
                    user="siteusr1" 
                    prompt=yes);
execute(
load table carload with
   index make
     on (make),
   index origin
     on (origin),
   index model
     on (model)
   as select *
   from cars3
) by sasspds;
disconnect from sasspds;
quit;

Load and Subset a Server Table

In this example, you create a subset of SPD Server table Cars3 using the LOAD TABLE statement. The new server table is named Fordcars. The SELECT statement specifies to include only columns Make, Model, Origin, Type, MSRP, and Invoice in the new server table. A WHERE clause specifies to include only rows that have Make="Ford". The creation of table Fordcar and its indexes occurs in parallel.

proc sql;
connect to sasspds (host="husky" 
                    service="spdsname" 
                    dbq="conversion_area" 
                    user="siteusr1" 
                    prompt=yes);
execute(
load table fordcars with
   index origin
     on (origin),
   index model
     on (model)) by sasspds;
select * from connection to sasspds
(as select Make, Model, Origin, Type, MSRP, Invoice from cars3
   where make="ford");
disconnect from sasspds;
quit;