Using SAS/ACCESS with Controlled Access Multiple Oracle Instances on DEC V AXlVMS Systems. Abstract

Full text

(1)

Using SAS/ACCESS with Controlled Access Multiple Oracle Instances on DEC V AXlVMS Systems

A. Richardson, Merck Sharp & Dohme.

Abstract

A method is described that enables SASIACCESS installed on DEC VAX!VMS systems to be used in conjunction with multiple Oracle instances under conditions where security considerations prevent the general user having direct knowledge of Oracle account names and passwords. Control of data extraction from a clinical trials system is used to illustrate the techniques involved.

Introduction

SASjACCESS [1] enables data held in non-SAS databases to be extracted directly into SAS datasets for further analysis or examination. When used with Oracle databases the basic SASIACCESS installation and configuration assumes the program is to operate in conjunction with a single Oracle instance where the user will know the names and passwords of the Oracle accounts from which they will extract data.

In many cases these assumptions will not be met due to installation and security considerations that give rise to (a) multiple Oracle instances, possibly at different revision levels, that are running Oracle applications where, (b) the Oracle account names and passwords are hidden from the user. For example, it is not uncommon to find development and production databases coexisting on a single system, and many Oracle applications use special 'Data Entry' or 'Query Only' accounts to prevent user accounts requiring high levels of account privileges by default In these cases the standard procedures for installing and using SAS/ACCESS with Oracle databases will not enable users to extract data from Oracle databases easily, and may compromise security if sPf!cial Oracle account names and passwords have to be made widely available.

This paper describes a method to overcome these problems when SAS and Oracle are operating on DEC V AX computers under the VMS operating system. Although the details of the method are only applicable to this operating system, the general principles may also be applicable to other operating systems where these products coexist.

Background and Methods

1: Using SAS/ACCESS with Multiple Oracle Instances

As with other programs interfacing with Oracle, SASIACCESS requires the program code used to communicate with the database management system to be linked with Oracle library routines to produce the final, usable, executable image. This linking is part of the SAS/ACCESS installation, and results in the production of the SASIACCESS executable image INTER.EXE. This program is used by SAS in response to PROC ORACEXT statements in SAS programs to extract data from. Oracle databases. An INTER.EXE produced by this procedure will only operate with Oracle instances at the

(2)

same revision level as the original Oracle system used for the linking, and could not be used with other Oracle instances. In those Situations where multiple Oracle instances exist at different reviSion levels, new versions of INTER.EXE will be required for each instance. In contrast, PROC ORACEXT can be used as part of any SAS program, and, on systetns running multiple Oracle instances, maybe required to extract data from databases associated with any or all of them. In order for SAS to resolve and action PROC OAACEXT statements without error, an appropriate version ofINTER.EXE must be used to obtain the data. By default, SAS expects to find INTER.EXE in one of the directories referenced by the SAS logical name SAS$LIBRARY, which is normally assigned at system startup into the system logical name table.

The Oracle revision level problem and any other problems arising from operating multiple Oracle instances with SAS under VMS, can be overcome by (a) using special SAS/ ACCESS directories containing copies of INTER.EXE linked to specific Oracle instances, .and (b) using SAS$LIBRARY to act as a switch to direct PROC OAACEXT calls to an appropriate INTER.EXE at runtime.

To implement the method the following baSic steps are required:

a: Create one directory per Oracle instance to hold the SAS/ACCESS program INTER.EXE,

b: For each Oracle instance, create a version of INTER.EXE linked to this Oracle instance in the directory created above,

c: Remove SAS$LIBRARY from the system logical name table to the job or

process logical name table, .

d: Provide a mechanism to reference the appropriate INTER.EXE directory in the SAS$LIBRARY asSignment statement prior to running SAS.

2:psing PROC ORACEXT with Controlled Access Databases

Acces~ t~

Oracle databases is controlled through the use of unique usemames and passwords before SQL or other statements can be proCessed, and as with other general interfacing products, SAS/ACCESS must provide this information to Oracle ru,fore PROC ORACEXT can begin to build SAS datasets from Oracle data. Thisinformation is provided to PROCORACEXT either in the form of procedure ()ptions (NAME=and PWD=), or within. a password file (PWD1'ILE.DAT) referenced by the logical name PWDFILE [1]. In both these cases the uSer Will have direct knowledge of the usemame and password for the Oracle database account •

... Security and data integrity conSiderations often give rise to Oracle applications with special, accounts, accessed indirectly by the general ~er, that are designed to enhance or re.strict the Ora<:J.efunctionality available to the user. A 'Data Entry' account requiring iusert and update privileges might

he

an e?llllllple of an account requiring eOhanced access, and a 'Query Only' account anexaniple of. an account requiring limited access. Knowledge of th~usern;.tIIlesand password.ofthistype of account are· usually restricted to the applicatlom'adtiuDiStrators, and are not made generally available. SAS/ACCESS can pose a securily riskfor these. applications.

Using a variety of VMS DCL(Digital Comma~d La!lguage) techniques SAS/ACC:E\SS can be made available to general users

ot

controlled access Oracle applications without them requiring direct knowledge of the special account usemames and

(3)

" ,;~ f' ;

1:

t,

~. ~: jj . . 1.

~

~

"

J~

t:

X

~

" f } ji ~ ~, F ~ t, "., j }' ~

r.-i

t\ ~"

""

~

\

i

~-\..

I

.~ ~'i ~: :f:

i

passwords, whilst a high level of security for this infonnation is maintained. By. placing the Oracle usemame and password in 'execute only' command files that build the PROC

ORACEXT job using temporary working files, SAS/ACCESS jobs can be run without the

user having direct knowledge of this account infonnation.

The method requires the following basic components for successful operation:

a: An execute only DCL command procedure, holding the applications' Oracle username and password,that builds and submits a PROC ORACEXT job to a suitable

batch queue in response to the users requeSt, .

b: Operating system mechanisms to control unauthorized use of the PROC ORACEXT DCL procedures,

c: Mechanisms to remove or replace temporary working files and SAS log files that

may contain password infonnation.

It is also helpful to provide methods to:

d: Incorporate user deftned SQL statements and SAS dataset infonnation into the PROC ORACEXT job,

e: Provide the_user with a list of the SAS dataset variable names upon successful completion of the job.

Implementation and Reslllts

MSDRL's Neuroscience Research Centre operates Oracle databases in conjunction with the SAS System for basic research, clinical trials and general administration. CUrrently three production Oracle systems and one development system are in operation supporting corporate clinical trials, UK clinical trials, basic research systems, and UK

medical systems development. All these databases hold data that has to. be transfe.rred to·

SAS for analysis, and all use Oracle security methods that restrict the knowledge of special

. account names and passwords

_to

applications supportstiU,f only. In addition to this

organizational separation of Oracle. systems, restrictions are aiso placed on the Oracle revision level for corporate systems to ensure compatability with systems world-wide, resultin~in different versions .of Ora<:le operating simultaneously.

The methods described above have been implemented.on.aD~C VAX-62~0

operating ~nderVMS Version S.0-2. The Oracle instances were at revision levels S •. 1.17

. (one instance) and S.!.22 (three instances) operating in conjunction with SAS at revision

levelS.IS. . .

1: Using SAS/ACCESS ~i'h Mlutiple Oracle instimces

The directory structure shown in Figure 1 holds the copies of INJER.EXE associated with e-ach of the NRC Oracle instances. These directories are located ina SAS/ACCESS directory that lias been added to the standard SAS directory structure, and uses a nlllning cpnvention based on the Oracle system identiftcation letter (Oracle SID) to associate each SAS/ACCESS area with the appropriate Oracle instance. Figure 2 shows the

DCL command procedure used to assign the SAS logical ~e SAS$LffiRARY (which

has bcenreinoved' from the system logical name table) priortorunrung SA.$.This DCL places SAS$UBRARY in the process logical name table, however other logical name tables could,be used.ifrequired.

(4)

r:~ F'

Ii

INTER.EXE INTER.EXE INTER.EXE INTER.EXE

Figure 1: SAS/ACCESS directory structure used to hold the copies of INTER.EXE associated with the four Oracle instances operated at the NRC. The use of the Oracle SID as the basis for the directory naming convention assists in optimizing the DCL required for the associated SAS$LffiRARY assignment (Figure 2).

To use the. copy of INTER.EXE associated with a particular Oracle instance the user defines the Oracle symbols and logical names, assigns SAS$LmRAR Y as above, and aetivates SAS. All subsequent SAS program references to PROC ORACEXT will then use the version of INTER.EXE referenced by SAS$LIBRAR Y to build the SAS dataset. In order to switch SAS/ACCESS to use an alternative INTER.EXE this manoeuvre needs only to be repeated before running SAS again.

2: Using PROC ORACEXT with Controlled Access Databases

The techniques described above to enable SAS/ACCESS to be used with controlled access Oracle databases

luis

been implemented for use with clinical trials databases using two DCL command files to ereate and run SAS/ACCESS jobs. The first of these command files creates the SAS PROC ORACEXT program, and passes this program to the second file which controls execution of the SAS job·in batch mode. Full details of these files, and the temporary file created during execution; are presented in the Appendix.

The fi r s t 0 f the seD C L com ma nd f He s (N R C f i I e SASACCESS_<app_name>.COM, where <app_name> is the name of the Oracle application) builds the specific.SAS/ACCESS job by performing the following tasks:

a: Creating a SAS. program fi.l~ in a semi-hidde~directory using a basic PROC. ORACEXT template to which is added a name for the SAS dataset and the name of the file containing the SQL to be used to .extract the data, both provided by the user,

b: Submitting the second DCL command file to a bat.cb queue for processing, passing to this command file theOrade instance system identification, theSASprogram filename, and the SAS dataset filename, .

Access to SASACCESS_ <app...:name>.COMfiles is strictly controlled. The VMS security scheme denies general aCcess to all users except the files' owner and SYSTEM (PROTECTION=(S:RWED,O:RWED,G,W». User access is then controlled through the use of an Access Control Entry (ACE) placed on the file. Each application has a unique identifier associated with it (of the form USER_ <app_name», and used as part of an ACE

(5)

$! ---$!

$!--- Obtain the Oracle SID. $!

$ osid=f$tm1nm("oracle$sid") $!

$! ---If the Oracle SID is not assigned ... $1

$ if (osid.eqs. "") $1

$!--- ... then assign the SAS version of SAS$LIBRARY. $! $ $ $1 then assign/nolog sys$disk:O,- sas$root:[procssas$root:[image ],-sas$extension:[load] sas$library $!--- ... else include the Oracle Instance INTER.EXE directory. $1 $ else $ assign/nolog sys$disk:Osas$root: [procs] ,-sas$root: [image ],-

sas$extension:[load],-sas$root: [sasaccess.oracle_' osid '] sas$library $ endif

$!

$!

---Figure 2: SAS$LIBRARY assignment. The DCL, which needs to be run

prior to running SAS, assigns the logical name SAS$LIBRAR Y to include, when appropriate, the directory for the Oracle instance in use prior to running SAS. This DCL operates in conjunction with the SASIACCESS directory structure shown in Figure 1; other directory structures would require appropriate modifications to be made to this code if the SASIACCESS directory structure followed other naming conventions.

with the characteristics IDENTIFIER=USER_ <app_name>, ACCESS=EXECUTE placed on SASACCESS_<app_name>.COM. Authorized application users then are granted the application identifier as part of their AUTHORlZE profile and can thereafter gain access to the procedure.

The second DCL command file (SASACCESS-,SUBMIT.COM) controls the execution of the SAS job by:

a: Establishing the required Oracle and SAS environment, b: Running the SASIACCESS job,

c: Removing all job specific files containing Oracle username and password infonnation, including the SAS log file,

(6)

d: Creating the final user job files.

Running these procedures creates one or two user files following execution. When the job has completed successfully the SAS dataset containing the data extracted from Oracle will be in a standard

*

.SSD file, together with a

*

.US file detailing the names of the SAS variables in the SAS dataset. No SAS log file will be present, as these files are removed by the batch job since they contain the Oracle username and password information.

If the job did not successfully complete a

*

.US file with an error message reporting

the fact will be present If the job fails because Oracle returns no reconts a null SAS dataset

will also be present; if other Oracle errors or DCL or VMS errors occur only the

*

.US file

will be present. This use of the

*

.US file ensures the user is consistently presented with at least one file of the outcome of the SAS/ ACCESS job and serves as a substitute SAS log

file. Details of the user interface, and examples of the

*

.LIS files from typical

SAS/ACCESS jobs are presented in Figures 3 and 4, Data extracted from Oracle using

these methods can then be used in other SAS programs by referencing the SAS dataset using the SET statement in the DATA step.

In order to operate the system with several applications each application must have its own copy of SASACCESS_ <app_name>.COM suitably configured for use with the application. Figure 5 shows schematically the multi-application system used at the NRC which is accessed via a SAS/ACCESS option menu for ease of use.

SAS/ACCESS Interface: BDB System Enter a name for the SAS dataset: SUBI89

Enter the name of the SQL file : SUBI89.sQL

SAS datasetfile will be : [RICHARDSON.SAS]SUBI89.SSD

SAS dataset contents files will be: [RICHARDSON.SAS]SUBI89.US

Job SASACCCESS_SUBMIT (queue SYS$BATCH, entry 228) started on SYS$BATCH

Press <rtn> to continue:

Job SASACCESS_SUBMIT (queue SYS$BATCH, entry 228) completed ...

Figure 3: User Interface. The tenninal screen output from a typical NRC application is shown.

(7)

1: Successful Completion

SAS 13:22THURSDAY,AUGUST31,19891

CONTENTS PROCEDURE

CONTENTS OF SAS MEMBER SASLlB.SUBI89 NUMBER OF OBSERVATIONS: 143 NUMBER OF VARIABLES: 18

MEMTYPE: DATA

# VARIABLE

--ALPHABETIC LIST OF VARIABLES AND ATIRIBUTES----TYPE LENGTH POSITION FORMAT INFORMAT LABEL 3 BOOK 14 COMMENTS 5 EXDATE 2 LNUMBER 16 OPDATE 17 OPERAT1 4 PAGE 18 PNUM 1 RECORD. 6 RES1 7 RES2 8RES3 9 RES4 10 RES5 11 RES6 12 RES7 13 RES8 15 SCIENTO NUM 8 CHAR 50 NUM 8 CHAR 16 NUM 8 CHAR 20 NUM 8 CHAR 10 NUM 8 CHAR 1 CHAR 10 CHAR 1 NUM 8 CHAR 10 NUM 8 CHAR 1 NUM 8 CHAR 20 2: Unsuccessful Co~pletion 24 95 40 8 165 173 32 193

o

48 49 59 60 68 78 86 87 145

SAS/Oracle Interface: 31-AUG-1989 13:25:15.96.

Error in SAS Procedure: Error creating SAS Da~et SU8189.SSD. a: Dataset may not exist (if no -.SSD file).

b: Dataset may be empty.

FiguI'e4 : :example.sof

*

.LIS files. Example 1 shows the

*

.LIS file produced following a successful SASIACCESS Interface job; Example 2 the file produced following an unsuccessful SASIACCESS Interface job.

65

OPERATION

(8)

A App.l App.2 App.N Application 1 Application 2 Application N Batch Processing *.LIS/SSD

Figure 5 : Schematic representation of the SAS/ACCESS system used to support the extraction of clinical and other application data from the NRC's Oracle databases for analysis by SAS. The solid lines represent penn anent files; the dotted lines temporary files. The user enters the system via a menu driven interface (A) from which each of the application copies of SASACCESS_ <app_name>.COM can be accessed (B). The menu system traps any file access violations and returns users to the menu system if attempts to use unauthorized applications are made. SASACCESS_ <app_name>.COM creates the temporarySAS program files (C) based on user and application requirements and passes details to SASACCESS_SUBMIT.COM (D) for processing. SASACCESS-,SUBMIT.COM produces tbe final output in the fonn of the

*

.LIS and

*

.SSD files in the user [.SAS] subdirectory (E). Addition of a new application to the system requires only that a new application specific copy of SASACCESS_ <app_name>.COM is made, and the new application added to the menu system.

(9)

',{

t

4:

;n f, .r: ~~ i

:r

~~

.

.;:-r

~,' ~; i1' 11 :f -, .~ t

"f

1

J

.g~ .~

:Jl

:!

F

h' E: .f~

·l

1,

~

\ ~. \. ,-Discussion

The SAS/ACCESS Interface product for Oracle offers a valuable method of extracting data easily from Ora'cle databases into SAS for further processing. SAS/ACCESS proVides a link: between tWo fundamentally different products, and its use can be problematic when used with certain Oracle configurations. These problems arise for two reasons. Firstly, as part of SAS, SAS/ACCESS exists in a form available for all SAS programs to use, whereas the Oracle database with which it has to interact may be one of several databases coexisting on the same system. Secondly, access to SAS/ACCESS will generally not be under any control (ie. anyone can use it), whilst access to Of;lCle databases always requires authorization. These differences can lead to problems when using SAS/ACCESS in Oracle environments consisting of multiple instances running applicatiolis where strict security has to be maintained on certain accounts. This paper describes a method to overcome some of these problems, enabling the full value and versatility of SAS/ACCESS to be used in these circumstances whilst maintaining any secUrity arrangements built into the Oracle applications.

The first problem, that of operating SAS/ACCESS in conjunction with multiple Oracle instances, has been solved by providing each Oracle instance with its own copy of the SAS/ACCESS program. By then employing mechanisms to dynamically assign (or . reassign) the SAS logical name SAS$LIBRARY, when SAS is invoked, to include the path to the appropriate directory, SAS/ACCESS can operate with any number of Oracle instances.

The need to provide individual copies of INTER.ExE for each Oracle instance is not strictly required 3.'1 only those instances running different versions of the Oracle code

require a new image. Experience suggests however that providing individual copies of INTER.EXE for each Oracle instance is desirable. Particularly, Oracle upgrade problems are restricted to specific instances only, and modification and testing, particularly of development databases, bas no knock-on effect onto other more critical systems. The major overhead in adopting this approach is the need to provide mechanisms to assign SAS$LIBRARY prior to running SAS. This, however, can be minimized by the inclusion of the necessary DCL in suitable command files activated at login or when the Oracle applications are run. .

The second potential probletp with SAS/ACCESS,as with all Oracle interfacing products, is the need to provide lJsemamesand passw!>rds to' the database management sys,tem before data ~

be

extracted trom. the database~ .When users have cO!Ilple~ access to passwords no problem exists, but many Sensitive or. cOll'!plex Qracle applicatio~suse

tec~niques tbat provide lil:cess ~special application accounts hidden from the user. At the

NRC all. clinical databases adopt these methods, and knowl¢ge of the application aCcount usemames and passwords is restri.cted to development and operations staff only.

Providing SAS/ACCESS functionality in this situation has been solved using DCL techniques that build special SAS/ACCESS jobs automatically and use batCh processing to run the SAS job. This approach enabies all functionality to be retained; whilst providing the necessary security for the Oracle applications.

WhilSt

iniproving security considerably .the method is not foolproof. Users have

to

have. the application usemames aqd passwords in read access files for at least the duration of the batch processing, and could therefore obtain them for use directly in PROC ORACEXT. However, by using semi-hidden directories fur processing (at the NRC all users have a scratch directory itldependent of their uller directories), and by appropriate post-job 'clearing rip',

this

information is avaHable only transiently, and only very experienced VMS wiers are likely to. find it. In

(10)

contrast. seeking this information at other times will not meet with success as the useinames and passwords are held in execute-only files. This technique offers some advantage over other suggested SAS/ACCESS methods to hold Oracle account names and passwords [1] (eg: the use of the 10gica:I name PWDFII...E and a password file). as in these cases the user must have pennanent read access to the password file.

The techniques described here also simplifies SAS/ACCESS support for .the Oracle application support staff. and offers some advantages for customizing SAS/ACCESS jobs. The requirement for each Oracle application to have its own single copy of SASACCESS_ <app_name>.COM enables the DCL to be located and supported as one of the application programs. and is therefore easily maintained. In particular. password management is simplified. with renewal of passwords being possible without user knowledge and with minima:l application downtime.

In addition to ease of maintenance. the need for one SASACCBSS_<app_name>.COM per Oracle application also enables the PROC ORACEXT program to be tailored to suit the particular application. The examples presented here assume the users will be developing SQL statements theinselves. and therefore makes use of the SAS %INCLUDE statement in order to incorporate these statements at runtime. However. there is no requirement for this to be the case. and SAS programs extracting pre-defined Oracle data. or running full SAS ana:lysis jobs could just as easily be the function of the PROC ORACEXT program. .

The techniques described here overcome some of the limitations of using SAS/ACCESS in multiple instance Oracle environments where access to applications is strictly controlled. Whilst the methods presented here apply only to SAS/ACCESS for Oracle on VMS systems. the genera:l principles may a:lso be applied to other SAS/Oracle interfacing tools [2]. or the other products in the SAS/ACCESS range.

References

[1] Angnymous. SAS Technica,i Report: P-18!. SAS/ACCESS Interface to Oracle. SAS InstitUte Inc.. Cary. North Carolina. USA. (1988). .

• [2] Anonymous~ OSTAT: A

Link

Between ORACLE and The SAS System. Software Interfaces Iilc .• Houston; Texas. USA. . . .

Aclmowledgement ".;."

Theau~or wouI4liketo~Mr.D.JO~On

for valuable. dlScussiOll8

~d ~J.B~

for

assistance with the preparation of the manuscript.

Quintiles(UI<;) Limited. 56, MinsterStteet; .

Reading.

.

B~rksbire. RGl 2lB.

United Ki[Igdom. ..

(11)

Appendix

The following sections present the details of the DCL command files and the temporary SAS program file used to run SAS/ACCESS jobs at the NRC. The following DCL is specific to the NRC: (1) say: write sys$output, (2) area: assigns all symbols and logical names for the program or utility passed as pI to the procedure, (3) utilities: logical name for the DCL utilities directory, (4) sys$scratch : logical name for the user scratch directory. The fIles created by these DCL fIles are automatically placed in a user SAS subdirectory, [.SASJ.

$1

$1--- Written by A.Richardson 1O-JUL-1989 15:59:30 $1

$I---Oracle application account information: $1--- osid : Oracle instance SID

$1--- onain : Application account name $1--- opwd : Application account password $1 $ $ $ $1 opid="U" onam="QRY_ACCOUNT" opwd="QRY_PASSWORD"

$1--- Obtain SAS dataset name (sasdataset) and $1--- SQL filename (sqlfilename) from user. $1 $ $ $ $ $ $1

say "SAS/ACCESS Interface: <Application Name>" say""

say nn

read!prompt="Enter a name for the SAS dataset :" sys$command sasdataset read!prompt="Enter the name of the· SQL file ." sys$command sqlfilename $1--- Extract user ID from usemame.

$1 $ $ $ $! uid=f$user() comma=f$locate(" ," ,uid)

pid=f$extract( comma+ 1,f$length(uid)-comma-2,uid) $!--- Inform user of files to be created.

$1 $ $ $ $ $ $ $! say 1111 say·lI.

say "SAS dataset file will be : ["+pid+".SAS]"+sasdataset+".SSD" say "SAS dataset contents file will be : ["+pid+".SAS],,+sasdataset+".LIS" say""

say lin

$!--- Create a unique filename (fnam) for the temporary file. $!

$ fnam="sasaccess"+f$cvtime(f$timeO,"ABSOLUTE" ,"DA Y")+ f$cvtime(f$time(), "ABSOLUTE", "HOUR")+

(12)

2', ~l f ~

~:

2

r:

!:

~~ .

1:

!:

"

k

~ ','. !' !e:

fi

I

~,

i,

g

t

I

f$cvtime(f$time() ... ABSOLUTE ... MINUTE .. )+ -f$cvtime(f$time() ... ABSOLUTE ... SECOND .. ) $1

$1---- Create the SAS temporary program file in the users scratch directory. $1 $ $ $ $ $ $ $ $ $1

open/write sasfile sys$scratch:'fnam' .sas write sasfile "OPTIONS OBS=O;"

write sasfile "LIBNAME SASLIB ·SYS$USER:(,,+pid+".SAS],;" write sasfile "PROC ORACEXT OUT=SASLIB. "+sasdataset

"+NAME='"+onam+''' PWD=· .. +opwd+ .. ··;" writesasfile "%INCLUDE "'+sqlfilename+"';"

write sasfile "PROC CONTENTS DATA=SASLffi."+sasdataset+";" write sasfile "RUN;"

close sasfile

$1--- Submit the SAS/ACCESS batch job with the required pai'ameters. $!

$ submit/nolo&...file/notify /parameters=('fnani' •• sasdataset' .' osid') -uti1ities:sasaccess_submit

$1

$!--- Let the user read the screen. say fill say Uti say"·" $! $ $ $ $ $1

inquire/nop.unctuation ans "Press <rtn> to cOntinue: " $!--- EndofDCL.

$1

2: SASACCESS_SUBMlT.COM

$1 . . . . . .'. .

$1--- Written by A.Richardson ll-JUL.1989 10:44:58 $!

$!---- Parameters:

$!--- pI ;::= SAS program filename. . .. , $1--- p2

=

Name for SAS dataset and related files. $1---- p3

=

Oracle instance SID.

$1 . . . . . • ... .

$1---- Assign the Oracle symbols and logical names for this job.

$1 .

$ areaoracle_'p3' $1

$1---- Assign SAS$LIBRARY for this job. $!

$ area sasaccess $!

$1--- Run the SAS/ACqsS job using the temporary SAS file. $1

$ sas sys$scratch:·pl·.sas $1

$1--- Delete

the

SAS/ACCESS temporary file. $1

(13)

J! j; t ,,!'. " .,: '~ : t,

,

{ -$:)

E

j)

~

,G

&

;\ ~ .J,

f

(. r ;':. ~. ,{ ;'

f

!; :j: ~

~

t

~~

t

~, j;

t

I

\ lc

'-'

~

~

~ ~ ~

~

$ $! delete sys$scratch:'pl '.S38;* $1---- Delete the SAS logfile. $1

$ $1

delete 'p l' .log; *

$1--- Create or rename a * LIS file for the user:

$1---If SAS job OK : Rename * .US file to match SAS dataset name.

$1---If SAS job not OK : Create * .LIS file with error message.

if (f$search(""pl'.lis").eqs."") then

opeD/write lisfile 'p2'.lis write lisfile "" $1 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $1

write lisfile "SASIOracle Interface: "+f$timeO+"." . write lisfile ""

write lisfile "Error in SAS Procedure: Error creating SAS Dataset."

write lisfile " a: Dataset may not exist (if no *.SSD file)."

write lisfile n b: Dataset may be empty."

else endif

write lisfile "" close lisfile

rename 'pl'.lis 'p2'.lis

$1---- End ofDCL. $1

3: SASACCESS25151856.SAS (Temporary File)

OPTIONS OBS=O;

LIBNAME SASLIB 'SYS$USER;[RICHARDSON.SAS],;

PROCORACEXT OUT=SASLIB.DATAI NAME='QRY_ACCOUNT' PWD='QRY_PASSWORD';

%INCLUDE 'SQL_DATAl.SQL';

PROC CONTENTS DATA=SASLffi.DATAli RUN;

This file was created\lsing the following responses for information requested by

SASACcEsS_ <app_name>.COM: Enter a name for the SAS dataset: DATAl

Enter the name of the SQL file :. SQL_DATA1.SQL

Figure

Figure 1: SAS/ACCESS directory structure used to hold the copies of  INTER.EXE associated with the four Oracle instances operated at the NRC

Figure 1:

SAS/ACCESS directory structure used to hold the copies of INTER.EXE associated with the four Oracle instances operated at the NRC p.4
Figure 3: User Interface. The tenninal screen output from a typical NRC  application is shown

Figure 3:

User Interface. The tenninal screen output from a typical NRC application is shown p.6
Figure 5 : Schematic representation of the  SAS/ACCESS system used to  support the extraction of clinical and other application data from  the NRC's  Oracle databases for analysis by SAS

Figure 5 :

Schematic representation of the SAS/ACCESS system used to support the extraction of clinical and other application data from the NRC's Oracle databases for analysis by SAS p.8

References

Updating...

Related subjects :