Creating a New Table

One of the SPD Server's strengths is its ability to create, manipulate, and query large tables. Generally, client users do not store large tables locally because of their size. The following examples assume that large tables are created and stored on the SPD Server host.

Creating a New Table Using Pass-Through Statements

Connect from a SAS session to an SPD Server SQL server. Execute a CONNECT statement. After making the connection, the first EXECUTE statement creates a table named LOTTERYWIN with two columns—TICKETNO and WINNAME. The second EXECUTE statement inserts an observation into the table where TICKETNO equals 1 and NAME equals Wishu Weremee.

The subsequent FROM CONNECTION TO statement retrieves all of the records from the new LOTTERYWIN table. (In this example, it retrieves a single observation inserted by the second EXECUTE statement. The DISCONNECT statement terminates the connection.

    proc sql;
    connect to sasspds (dbq='mydomain'
                        host='workstation1' serv='spdsname'
                        user='me' passwd='luckyones');
    execute (create table lotterywin
        (ticketno num, winname char(30))) by sasspds;
    execute (insert into lotterywin
        values (1, 'Wishu Weremee')) by sasspds;
    select * from connection to sasspds
        (select * from employee);
    disconnect from sasspds;
    quit;

Creating a New Table with a LIBNAME Statement

SITEUSR1 creates a new SPD Server table named CARDATA.OLD_AUTOS on the server.

LIBNAME cardata sasspds 'conversion_area' server=husky.5105
     user='siteusr1' prompt=yes;

/* Create the table CARDATA.OLD_AUTOS on the SPD Server host. */

data cardata.old_autos;
     input year $4. @6 manufacturer $12. model $12. body_style $5.
     engine_liters @39 transmission_type $1. @41 exterior_color
     $10. options $10. mileage conditon;

datalines;

1966 Ford        Mustang      conv  3.5  M  white     00000001 143000 2
1967 Chevrolet   Corvair      sedan 2.2  M  burgundy  00000001  70000 3
1975 Volkswagen  Beetle       2door 1.8  M  yellow    00000010  80000 4
1987 BMW         325is        2door 2.5  A  black     11000010 110000 3
1962 Nash        Metropolitan conv  1.3  M  red       00000111 125000 3
;