Microsoft Access

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. 

Because Microsoft Office is so widely used, it is sometimes necessary for you to import data directly from Microsoft Excel or Microsoft Access. Excel files make for a poor database, however. First, Excel files are almost guaranteed to come from a system that is not compliant with CFR 21 - Part 11. Second, it is often the case that the Excel files were created in such a way that the data are not WYSIWYG ( what you see is what you get ). In other words, each cell in Excel could be entered with a different Excel format, which you would not see until you either reformat an entire column in Excel or try to have some other software like SAS read the contents of the Excel file. For these reasons, it is best not to accept Microsoft Excel data as a data source for clinical trials if at all possible. 

In Microsoft Access the lab normal data might look like this ... 

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 normals ranges.mdb Microsoft Access file could be read in with the following similar SAS code. 

Program 3.7 Using the LIBNAME Statement to Read Microsoft Access Data... 

Again, the ACCESS specification as a LIBNAME engine is optional, as the libref would default to Microsoft Access because .mdb is in the physical filename. Note that the ACCESS LIBNAME engine seems by default to import all text fields as 255... 

The Import Wizard process for Microsoft Access files works like the one for Excel files and produces similar PROC IMPORT code. Keep in mind that text fields get a default length of 255 characters when PROC IMPORT is used with Microsoft Access files. PROC IMPORT adds a number of file security options, as well as the ability to scan memo fields via the SCANMEMO option to determine the width of character fields in Microsoft Access files. 

The process for importing Microsoft Access files into SAS Enterprise Guide is much like the process in the previous Excel example. 

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

Because the Microsoft Office suite is so widely used, it is sometimes necessary for you to export SAS data sets into Microsoft Access or Microsoft Excel files. SAS provides the Export Wizard/PROC EXPORT and the SAS Enterprise Guide interface for exporting data directly to Microsoft Office files. 

SAS Enterprise Guide 3.0 offers a simple, convenient export tool for Microsoft Access and Excel files. To export the DM data set to Microsoft Excel in SAS Enterprise Guide, simply select the file in the Process Flow window, click File, and select Export dm from the menu as follows ... 

Your dm.xls Excel file is then created. The process of exporting a Microsoft Access database file is just as easy, requiring only a few clicks of the mouse. 

Using the SQL Pass-Through Facility to Read Microsoft Access Data 64... 

QuanOptimize from Micromass also allows automated method development for quantitative LC/MS/MS. It automatically identifies the best method for each compound, then runs batches of samples for quantitative analyses and report results in a QuanLynx browser. Thermo recently launched a similar product for automatic MS tuning. Known as QuickQuan, it generates data and stores it in a central Microsoft Access or Oracle database for future access. The infusion-based valve switching auto-tuning device allows individual compounds to be fully and automatically optimized in about 1 min. 

You must have Microsoft Access 2000 installed on your computer in order to open this database. Two versions of this database are available ... 

Microsoft Access 2003 serves as database system and provides the planner with the user interface. 

A database such as Microsoft Access would be able to do this Boolean search with ease. The Filter by Form, the Filter by Selection, and the Advanced filter/sort function allow the user to specify density >0.6 AND <0.7 or bp >-40 AND <0. This search yields three results ... 

You must have Microsoft Access installed on your computer if you wish to modify the contents of the database. This is a necessary tool for maintaining a company database. If you do not have Microsoft Access, you can still use the database with Capture CIS and you can still download parts from the Internet. However, you will not be able to modify the downloaded data unless you have Microsoft Access. 

MS Access Database Microsoft Access Driver p.mdb) Text Rles Microsoft Text Driver ("tat csv)... 

Select Microsoft Access Drlveri. imlbl and then click the Finish button ... 

Microsoft dBase Driver f. dbf) Microsoft dBase VFP Driver f. dbf) Microsoft Access Driver f.mdb) Microsoft Access Driver f.mdb) Microsoft Access Oriverf.mdb) Microsoft Excel Driver f xls)... 

The user portion of the database is where you will save parts for your company. Notice that the USER portion is split into subsections. When you download parts from Activeparts, they will be added to the USER section. Using Microsoft Access, you can place them in one of the existing subsections, or create new subsections. Select the Hardware subsection ... 

The next thing that we would like to do is view our database and see where the part was added. Save the project and then run Microsoft Access. Open file DIGIKEY SC.mdb. It should be located in the Capture Database directory in the... 

Close the table and then close Microsoft Access. Switch to the schematic view of Oread Capture CIS and then type z to place a database part ... 

To add a part to the database we must create a symbol for the part using Oread capture, unless it is a standard symbol that already exists, and then we must add the part information to the database using Microsoft Access. We will show how to create a new library, add a new symbol to that library, and then add the part information to the Digikey database. 

E.2. Adding a Part to the Database Using Microsoft Access... 

Run Microsoft Access, open the file DIGIKEY SC.mdb, and then open the USER table as shown on pages 578-579 ... 

The Microsoft Access database (http //www.microsoft.com/) is a collection of data and objects related to a particular topic (Hutchinson and Giddeon, 2000). The data represent the information stored in the database, and the objects help users define the structure of that information and automate the data manipulating tasks. Access supports SQL (Structured Query Language) to create, modify, and manipulate records in the table to facilitate the process. It is a table-oriented processing. The user is referred to the Microsoft Access User s Guide or online Help for information. 

---