ODBC Connectivity to SAS at Chesapeake Bay Program
Table of Contents
1. Creating SAS Transport files for import into PC-SAS
2. MS-ACCESS Connectivity to PC-SAS on same WIN95 PC
Setting up ODBC linkage on PC
Import or Link SAS data set to ACCESS Tables
3. MS-ACCESS Connectivity to PC-SAS on remote WIN95 PC on CBP LAN
Setting up ODBC linkage on PC
Import or Link SAS data set to ACCESS Tables
4. Setting up the SAS/SHARE server under VMS, using Multinet TCPIP
Multinet Services set-up
SAS/SHARE Server (PROC Server)
Server Management (PROC Operator)
5. Setting up the Services on the WIN95 PC to access VMS-SAS/SHARE Server
6. MS-ACCESS Connectivity from WIN95 PC to VMS-SAS data sets
Setting up ODBC linkage on PC
Import or Link SAS data set to ACCESS Tables
1. Creating SAS Transport files for import into PC-SAS
Use the following procedure to move a SAS file from an external location to a PC SAS file that can be imported into MS-ACCESS. This code zips SAS data sets into an XPORT file on the Alpha.
PC-SAS COPY/XPORT example code
This code creates a VMS SAS transport file using the XPORT engine
libname org '[directory_name]';
libname tgt xport '[]myfile.dat' cc=none;
proc copy in=org out=tgt memtype=data ;
select o266268 o269270 o271273;
* (3 SAS data sets are zipped into same XPORT file named myfile.dat);
endsas;
The following SAS code imports the SAS data sets out of the XPORT file into the c:\data\ directory on a PC running PC-SAS;
libname new 'c:\data\';
libname in1 xport 'c:\data\myfile.dat';
proc copy in=in1 out=new;
run;
2. MS-ACCESS Connectivity to PC-SAS on same WIN95 PC
A. Setting up ODBC linkage on PC
PC-SAS and SAS ODBC must be loaded on the user’s PC. Each WIN95 PC user must define the ODBC linkage to the directory that contains the desired SAS data sets. A new SERVER must be defined to ODBC:
1. Open the Control Panel and double click 32bit ODBC.
2. Select Add, SAS and then Finish.
3. Select the General Tab and configure the following entries:
Data Source Name: 1-8 character user defined identifier, such as:'my_odbc'
Description: (Optional) Supply a description that tells users something about the data source they have selected.Select the Server Tab and configure the following entries:
Server: 1-8 character server identifier, such as: ‘my_data’
Password:
Protocol: dde
SAS Path: c:\Program Files\sas\sas.exe
Working Directory: c:\Program Files\sas\
Single User Local Server: true (selected)
Select the Library Tab and configure the following entries:
Library: my_data (1-8 character user defined library identifier. Use a different library name for each directory that you want to access on the PC.)
Host File Name: my_PC_disk:\my_directory_of_choice\ (ex: c:\data\)
Select the General Tab and configure the following entries:
Server: Choose the server name as defined above (ex: my_data)B. Import or Link SAS data sets to ACCESS Tables
In MS-ACCESS,
1. Select File, Get External Data, Import or Link Tables
(Linking Tables to a SAS data set allows direct access and editing of the SAS data set through MS-ACCESS).
2. Choose 'Files of Type: ODBC' and the Select Data Source window will appear.
3. Select 'Machine Data Source' tab.
4. Select the Data Source Name, which was identified during ODBC setup and click on OK.The ODBC driver will then import the SAS data into the current Access database and creates a new Access table with a copy of the SAS data.
3. MS-ACCESS Connectivity to PC-SAS on remote WIN95 PC on CBP LAN
Importing or Linking to a SAS data set on a remote PC on the local LAN can be accomplished by:
1. Turning on SHARING on the remote PC by clicking on the C: in Windows Explorer and setting sharing on for the user who needs access.
Example: on workstation "CBP245"1. Click on the c: drive icon in Explorer.
2. Right click: Sharing
3. Shared as: mycbp_c 4. Add -user names- and -desired level of access- for each user who is allowed access.2. Mapping a drive letter to the remote PC. Do this by selecting Tools: Map Network Drive in Windows Explorer. Choose an unused letter, such as, G or H and map that to the remote directory, which holds the files of choice.
Example g: = \\cbp245\mycbp_c which maps the remote shared PC c: drive defined above as your local g: drive.
A. Setting up ODBC linkage on PC
This newly mapped g: drive can now be defined as another ODBC library as described in 'SAS ODBC Driver Tech Report: User's Guide and Programmers Reference’ pages 9-17. Follow the example described in Section II.A.
B. Import or Link SAS data set to ACCESS Tables
SAS files in this new library can now be accessed, as described above for files local to your own c: drive.
4. Setting up the SAS/SHARE server under VMS, using Multinet TCPIP
The CBPO Alpha runs on the VMS operating system. TCPIP connectivity is provided through a commercial product named Multinet. Multinet ‘Services’ must be configured on the Alpha by the System Administrator to allow ODBC connectivity to a SAS/SHARE server running on the Alpha.
A. Multinet Services set-up
1. Multinet TCP Services for SAS/SHARE are set up in the MULTINET:HOSTS.LOCAL. Edit this file and add the following line: "SERVICE : TCP : 5000 : sasshare : "
2. Recompile the host tables by typing: $MU HOST_TABLE COMPILE
3. Install the new tables by typing: $@MULTINET:INSTALL_DATABASES
4. Restart the server by typing: $@MULTINET:START_SERVERB. SAS/SHARE Server (Proc Server)
The following SAS code is used by the Alpha SAS System Administrator to initiate the SAS/SHARE server:
OPTIONS COMAMID=TCP; *Specify the transport protocol;
PROC SERVER *Start the server process;
ALLOC *Allow users to define libraries;
LOG=(CPU,IO) *Logging options;
OAPW=PASSWORD *Operate Procedure Password;
SERVERID=SASSHARE *Server ID, must match the TCP service process;
UAPW=PASSWORD; *ODBC driver password;
RUN; *Program execution;C. Server Management (PROC Operator)
The following SAS code is used by the Alpha SAS System Administrator to manage the SAS/SHARE server:
OPTIONS COMAMID=TCP; - Specify the transport protocol
PROC OPERATE; - Start the Operate process
SAPW=PASSWORD; - Server Access Password, same OAPW
SERVERID=SASSHARE; - Server ID, must match the TCP service process
DISPLAY SERVER; - (Optional) Writes server info to current log file
QUIESCE SERVER; - (Optional) Gradually terminates the server
START SERVER; - (Optional) Restarts a server resource
STOP SERVER; - (Optional) Stops the server
5. Setting up the Services on the WIN95 PC to access VMS-SAS/SHARE Server
Services on a WIN95 machine, that need ODBC connectivity to the SAS/SHARE server, must be configured via the c:\windows\services file. The following line must be added to this file:
sasshare 5000/tcp
After modifying the file, the PC must be rebooted for the changes to take effect.
# Copyright (c) 1993-1995 Microsoft Corp.
#
# This file contains port numbers for well-known
# services as defined by RFC 1060 (Assigned Numbers).
#
# Format:
#
# <service name> <port number>/<protocol> [aliases...] #[<comment>]
#
echo 7/tcp
ftp 21/tcp
telnet 23/tcp
smtp 25/tcp mail
time 37/tcp timserver
…
…
sasshare 5000/tcp
…
…
rmt 5555/tcp rmtd
6. MS-ACCESS Connectivity from WIN95 PC to VMS-SAS data sets
A. Setting up ODBC linkage on PC
Each WIN95 PC user must define the ODBC linkage to the SASSHARE server on the CBPO ALPHA. The process is the same as described above, but a new SERVER must be defined:
1. Open the Control Panel and double click 32bit ODBC.
2. Select Add, SAS and then Finish.
3. Select the Server Tab and configure the following entries:Server: chesie.sasshare4. Select the Libraries Tab and configure the following entries:
Password: password
Access Method: TCP
Multiuser Server: true (selected)
Click on the Configure button and configure the following entries:
Server Address: CHESIE.ANN.EPA.GOV or 204.47.238.19
Server User Name: Alpha/Chesie account username
Server User Password: Alpha/Chesie account passwordLibrary Name: my_data (1-8 character user defined library identifier. Use a different library name for each directory that you want to access on the Alpha.)5. Select the General Tab and configure the following entries:
Host File Name: alpha_drive_spec:[directory_spec] i.e. userdisk:[aharding]
Description: (Optional)Data Source Name: chesie.sasshare
For example, if you are defining SAS data that are stored on a machine named CHESIE, you might call your data source SAS_CHESIE.
Description: (Optional) Supply a description that tells users something about the data source they have selected.B. Import or Link SAS data sets to ACCESS Tables
In MS-ACCESS,
1. Select File, Get External Data, Import or Link Tables
2. Choose 'Files of Type: ODBC' and the Select Data Source window will appear.
3. Select 'Machine Data Source' tab.
4. Select the Data Source Name, which was identified during ODBC setup and click on OK.The ODBC driver will then import the SAS data into the current Access database and creates a new Access table with a copy of the SAS data.