Accessing SPD Server from a SAS Client

SQL Pass-Through Facility

To connect to an SPD Server SQL server from a SAS session, you must submit a CONNECT statement that specifies the SASSPDS engine and SPD Server options and then issues the SQL commands.
For example:
   PROC SQL;
    connect to sasspds
      (dbq='mydomain'
       host='namesvrID'
       serv='5555'
       user='neraksr'
       passwd='siuya');
    select *
      from connection
      to sasspds
       (select * from employee_info);
    disconnect from sasspds;
    quit;

LIBNAME Access

Overview of LIBNAME Access

A logical name or libref is a name for the data library that you associate with an SPD Server domain during a SAS job or session. After a libref is assigned, SPD Server enables you to read, create, or update files in the data library if you have the appropriate access to the data library.
A libref is valid only for the current SAS job or session. Librefs can be referenced repeatedly during a valid job or session. SAS does not limit the number of librefs that you can assign during a session. After you define a libref, it is most commonly used as the first element in two-level SAS filenames: LibraryName.Tablename. The library name or libref identifies where the SPD Server can find or store the file.
LIBNAME libref SASSPDS <'SAS-data-library'> <SPD Server-options>;
Use the following arguments:
libref
a name up to 8 characters long that conforms to the rules for SAS names.
SASSPDS
the name of the SPD Server engine.
'SAS-data-library'
the logical LIBNAME domain name for an SPD Server data library on the host machine. The name server resolves the domain name into the physical path for the library.
SPD Server-options
one or more SPD Server options.
The section, Using a LIBNAME Statement to Access SPD Server contains examples of LIBNAME connections to SPD Server.

Example Using a Libref with LIBNAME Access

The following statement creates the table TRAVEL and stores it in a permanent SAS library with the libref ANNUAL:
data annual.travel;
The following is a LIBNAME statement that associates a libref, the SASSPDS engine, and an SPD Server domain:
LIBNAME mydatalib sasspds 'mydomain'
   host='namesvrID'
   serv='5555'
   user='neraksr'
   passwd='siuya';

LIBNAME Options

You must supply the SASSPDS engine name to access SPD Server LIBNAME domains with a LIBNAME statement. You must specify one or more SPD Server options. Here is the syntax for an SPD Server option:
<SPD Server-option>=<value>;
SPD Server-option
a keyword to name the option
value
a value expected by the keyword
Option values in a LIBNAME statement enable the engine to initiate, manage, and customize a client session.

Connect to a Specified SPD Server Host

Overview of Connecting to a Specified SPD Server Host

To connect to a host, SPD Server needs the network node name for the SPD Server host machine or the IP address of the server machine, and the port number of a name server. SPD Server provides the following options to locate a name server using a named service:
SERVER=
specifies a node name for an SPD Server host machine and a port number for the name server that is running on the machine.
HOST=
specifies a node for an SPD Server host machine and a port number for the name server that is running on the machine.
Both options have the same function. SERVER= arguments are compatible with SAS/SHARE software. HOST= arguments support FTP conventions. You can use the HOST option to specify an IP address (for example, 123.456.76.1) for the node. The SERVER option requires a network node name.

SPDSHOST= Macro Variable

If you create a SAS macro variable named SPDSHOST= or an environment variable named SPDSHOST=, then whenever a LIBNAME statement does not specify an SPD Server host machine, SPD Server looks for the value of SPDSHOST= to identify the host server.
   %let spdshost=samson;
     LIBNAME myref sasspds 'mylib'
     user='yourid'
     password='swami';
The first statement assigns the SPD Server host SAMSON to the macro variable SPDSHOST. Therefore, a subsequent LIBNAME statement does not need to name the host server again.

Validating the Client User ID

SPD Server uses ACL file security to secure domain resources. If ACL file security is enabled, the SPD Server grants access in the following order:
  1. uses the permissions that belong to the UNIX ID that is associated with the SPD Server
  2. uses the permissions that belong to the SPD Server user ID
You can use SQL pass-through and LIBNAME statement options to specify the identity of an SPD Server user. SPD Server uses a special ID table to validate user IDs and passwords. The following LIBNAME options identify a client:
ACLGRP=
specifies one to five ACL groups that the user can belong to.
ACLSPECIAL=
grants special privileges to an SPD Server user who was previously set up as special. (ACLSPECIAL=YES is defined for the user in the password file.) Special privileges override other ACL restrictions that apply to resources in the domain.
CHNGPASS=
prompts a client user to change his or her SPD Server password.
NEWPASSWORD= or NEWPASSWD=
specifies a new password for an SPD Server client user.
PASSWORD= or PASSWD=
specifies a password to validate an SPD Server client user.
PROMPT=
prompts for a password to validate an SPD Server client user.
PASSTHRU=
specifies implicit SQL pass-through options for an SPD Server client user.
USER=
specifies the SPD Server user ID.
User ID Options When ACL File Security Is Enabled
USER=
PASSWORD= or PROMPT=
Access
Required unless the SAS client process has a user ID (the SAS client process is not a Windows client). Values submitted for USER= are validated against the SPD Server user ID table.
Required and validated against the SPD Server user ID table.
Resources that you create within the SPD Server LIBNAME domain and in other resources that are not excluded by ACLs or by UNIX file permissions.
User ID Options When Only UNIX File Security Is Enabled
USER=
PASSWORD= or PROMPT=
Access
Not required. The SPD Server user ID that is under only UNIX file security is anonymous.
Not required with the anonymous user ID.
All resources within the LIBNAME domain granted access by UNIX permissions for the SPD Server UNIX ID.

Manage Server Network Traffic

If your SPD Server installation uses the same physical machine to run your SPD Server client process and your SPD Server host services, you can use the following SPD Server options to improve client/server network traffic:
NETCOMP=
compresses the data stream in an SPD Server network packet.
UNIXDOMAIN=
uses UNIX domain sockets for data transfer between the client and the SPD Server.

Additional LIBNAME Options

BYSORT=
performs an implicit sort when a BY clause is encountered.
DISCONNECT=
specifies when to close network connections between the SAS client and the SPD Server. Closure can occur after all librefs are cleared or at the end of a SAS session.
ENDOBS=
specifies the end row (observation) in a user-defined range.
NOSASSORT=
ignores an explicit PROC SORT statement.
STARTOBS=
specifies the start row (observation) in a user-defined range.
TRUNCWARN=
suppresses hard Read and Write stops when NLS transcode overflow or character mapping errors occur. When you specify the TRUNCWARN=YES LIBNAME option, data integrity can be compromised because significant characters can be lost in this configuration. The default setting is NO, which causes hard Read and Write stops when transcode overflow or character mapping errors occur. When TRUNCWARN=YES and a transcode overflow or character mapping error occurs, a warning is posted to the SAS log when the data set is closed if overflow occurs, but the data overflow is lost.

Examples of the LIBNAME Statement

Example 1

Example 1 creates the libref MINE, associates it with the SASSPDS engine, and specifies the SPD Server LIBNAME domain GOLDMINE. Values for the SPD Server options perform the following tasks:
  • locate the server machine FASTCPUS and use the default service SPDSNAME to get the port number of the name server
  • validate the SPD Server user EXPLORER
  • prompt for user’s old SPD Server password
  • change the password
LIBNAME mine sasspds 'goldmine'
  user='explorer'
  host='fastcpus'
  prompt=yes
  chngpass=yes;

Example 2

Example 2 represents the first LIBNAME statement that was made for the SPDSDATA domain. The example creates the libref MYLIB, associates MYLIB with the SASSPDS engine, and specifies the SPD Server LIBNAME domain SPDSDATA. Values for the SPD Server options perform the following tasks:
  • locate the server machine HEFTY and use the named service SPDSNAME to get the port number of the name server.
  • validate the SPD Server user ID camills and account password escort.
LIBNAME mylib sasspds 'spdsdata'
  server=hefty.spdsname
  user='camills' password='escort';