SQLs

Among many approaches to manipulating a relational database, the most prevalent one is a language called SQL (Structured Query Language) [2]. 

The relational database model was developed by Codd at IBM in 1970 [9]. Oracle provided the first implementation in 1979. The hierarchical database IMS was replaced by DB2, which is also an RDBMS. There exist himdreds of other DBMSs, such as SQL/DS, XDB, My SQL, and Ingres. 

Relational model ease of use without in-dcpih knowledge high degree of data independence SQl capability less cflicicnt lhan other miodels integrity problems possible to misuse... 

Another important feature of a LIMS DBMS is the abiUty to perform ad hoc database queries. It is impossible to predict all the forms in which LIMS users will want to display their data while the LIMS is being designed. As a result, it is desirable to select a LIMS which allows users to define their own reports. Most commercial DBMS have a standard query language (SQL) interface. SQL is a simple database query tool which is based on... 

English-language commands. This sample SQL query says, "Give me all sample request data containing John Smith as the customer."... 

Figure 10-132. Cooling water requirements for cooler of Figure 10-131. (Used by permission SQL Technic, Inc., Karbate Division.)...

Are records protected during record retention period Accessible by database commands, SQL, etc. ... 

Importing Relational Databases and Clinical Data Management Systems 42 SAS/ACCESS SQL Pass-Through Facility 42 SAS/ACCESS LI BN AM E Statement 43 Importing ASCII Text 44... 

PROC IMPORT and the Import Wizard 44 SAS DATA Step 52 SAS Enterprise Guide 53 Importing Microsoft Office Files 56 LIBNAME Statement 58 Import Wizard and PROC IMPORT 59 SAS/ACCESS SQL Pass-Through Facility 64 SAS Enterprise Guide 65 Importing XML 68... 

In most cases, the data that you use for clinical trial analyses are found in some kind of computer file external to the SAS System. The data you need may be found in a permanent SAS data set, a relational database table found in Oracle or Microsoft SQL Server, a Microsoft Access or Excel file, a simple delimited ASCII text file, or even an XML file. In any case SAS provides a wide array of ways in which to import data files into SAS. We explore these tools and the advantages and disadvantages of each in this chapter. 

Most clinical data management systems used for clinical trials today store their data in relational database software such as Oracle or Microsoft SQL Server. A relational database is composed of a set of rectangular data matrices called tables that relate or associate with one another by certain key fields. The language most often used to work with relational databases is structured query language (SQL). The SAS/ACCESS SQL Pass-Through Facility and the SAS/ACCESS LIBNAME engine are the two methods that SAS provides for extracting data from relational databases. 

Program 3.1 Using the SQL Pass-Through Facility to Get Data from Oracle... 

In this program the oratabs libref allows all of the tables found in that Oracle data instance to be treated like SAS data sets. This is a simple and fast way of accessing relational databases, and it requires no knowledge of SQL to implement. 

Although the preceding examples import Oracle data, SAS/ACCESS can be used to access quite a number of relational databases, including Oracle, Microsoft SQL Server, Sybase, DB2, and Informix. The database-specific details on how to set up these SAS/ACCESS connections can be found in the SAS/ACCESS product documentation. 

Some commonly used SAS tools for importing Microsoft Excel and Microsoft Access data into SAS include the LIBNAME statement, the Import Wizard/PROC IMPORT, the SQL Pass-Through Facility, and SAS Enterprise Guide. 

The SAS/ACCESS SQL Pass-Through Facility is another way for SAS to dynamically establish a connection to Microsoft Excel or Access files. You can connect to the Microsoft Excel file normal ranges.xls by using the following SAS code. 

OBTAIN AVAILABLE WORKSHEET NAMES FROM EXCEL FILE proc sql ... 

MySQL Microsoft SQL Server Teradata SAS/ACCESS interface to PC file formats reads Microsoft Access and Excel files directly... 

Imagine you have a data set of adverse event data and a data set of concomitant medications, and you want to know if a concomitant medication was given to a patient during the time of the adverse event. The following program defines the two data sets and joins them with PROC SQL so that you get all medications taken during any specific... 

Program 4.8 Performing a Many-to-Many Join with PROC SQL... 

KEEP MEDICATIONS THAT STARTED OR STOPPED DURING AN ADVERSE EVENT OR ENTIRELY SPANNED ACROSS AN ADVERSE EVENT. proc sql ... 

Here you can see that each adverse event that was experienced has now been merged with each concomitant medication taken during that time. This is due to the SQL SELECT statement ON clause, which allows for complicated logic to be applied to the join. 

Using SQL, you can create very sophisticated database joins that were not easily done before SQL became part of Base SAS. Keep in mind that these data set joins can get very complex. For example, the previous scenario gets quite a bit more complicated with the introduction of missing start and stop dates, which are a common occurrence in clinical trial data. 

The following example relies on more PROC SQL, a little DATA step programming, a few SAS macro variables, and a final PROC REPORT for table presentation. Here are the concomitant medication summary annotated SAS program, notes for the program, and the output. 

N2 = 2ND COLUMN N FOR PLACEBO, N3 REPRESENTS THE 3RD COLUMN TOTAL N. proc sql noprint ... 

---