Connecting to Local Data (Single-User Server)

Goal

You want your application to use the Base SAS provider to start a local server for use by a single user. In this context, a local installation of Base SAS operates as the server.

This recipe applies to the Base SAS provider. Sample code for ADO is included.

Implementation

Adding Server Information to the TCP/IP Services File

Before you can open this connection, you must modify the TCP/IP services file to include an entry for the SAS server (that is, the Base SAS installation) that the provider will use. The TCP/IP services file contains information about the services available on the local machine, including available SAS servers. For each named service, the file specifies a port number, a protocol name, and any service alias.

Note: The TCP/IP services file is not stored in the same location on all platforms. However, for the Windows NT, Windows 2000, and Windows XP platforms, the services file is stored at c:\winnt\system32\drivers\etc\services.

Entries in the services file have the following general form:

<service-name> <port-number/protocol-name> <aliases> # <comments>

To add the server information to the file:

Enter the server ID as the service name. The server ID is usually a case-sensitive string that is one to eight characters in length. The first character is a letter or an underscore; the remaining seven characters can include letters, digits, underscores, the dollar sign ($), or the at (@) symbol.

Note: When you write the code to start the server, you use the same server ID that you enter as the value for the "Data Source" property. You also enter the server ID as the value to be passed into the %sasodbc macro for the "SAS Parameters" property as shown in the sample code (see Sample Code for Starting a Local Server).
Set the port number to a number above 1024 that is not already in use. Any port number that is equal to or less than 1024 is reserved. For larger networks, obtain the port number from your network administrator.
Set the protocol name to TCP.

For example, to configure the Base SAS provider to access a local server named sdplserv, you add the following entry to the services file (substituting the appropriate port number):

sdplserv		5420/tcp	# Base SAS Provider Local Server

Sample Code for Starting a Local Server

After you add the server entry to the TCP/IP services file, you can use the following Visual Basic code to start the local server. This sample uses the Connection object Properties collection to specify individual property values. The server ID is sdplserv (which is the default).

Dim obConnection As New ADODB.Connection

obConnection.Provider = "sas.BaseSASProvider"
obConnection.Properties("Data Source") = "sdplserv"
obConnection.Properties("SAS Executable") = "C:\\Program Files\\SASHome\\SASFoundation\\9.3\\sas.exe"
obConnection.Properties("SAS Parameters") = "-initstmt %sasodbc(sdplserv) -icon -nologo -notutorialdlg"
obConnection.Properties("SAS Working Directory") = "C:\\Program Files\\SASHome\\SASFoundation\9.3\\"
obConnection.Open

Tip

To identify a version of Base SAS that is older than Version 9 (the default), provide a value for the "SAS Server Release" property. For information about using this property, see Connecting to a Specific SAS/SHARE Server Version.

For an explanation of the properties used in the sample code, see Base SAS Provider Properties.

A Closer Look at the Value of the 'SAS Parameters' Property

The following line of code specifies the default value for the "SAS Parameters" property.

obConnection.Properties("SAS Parameters") = "-initstmt %sasodbc(sdplserv) -icon -nologo -notutorialdlg"

The initialization statement -initstmt executes a SAS macro named %sasodbc, which in turn invokes the server identified by sdplserv. The -icon option immediately minimizes the SAS session. The -nologo option suppresses the SAS logo and copyright information. The -notutorialdlg option suppresses starting the SAS tutorial.

The %sasodbc macro ships with Base SAS and is found in !sasroot\core\sasmacro\sasodbc.sas. This macro, which was created for use with the SAS ODBC driver, executes PROC ODBCSERV. To specify PROC ODBCSERV options in addition to the sdplserv server ID, you can modify the sasodbc.sas file. You can also modify sasodbc.sas in order to include additional SAS system options or SAS statements such as the LIBNAME statement.

CAUTION:

If you also use the SAS ODBC Driver, you should create a separate SAS macro file for use with the Base SAS provider.

Note: The PROC ODBCSERV options are identical to the PROC SERVER statement options. For more information about the options, see the SAS/SHARE User's Guide.

Note: !SASROOT is the logical name for the directory in which you install SAS.