Microsoft Office Files

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. 

SAS provides several ways to read Microsoft Excel and Access files. We cover many of these import methods here using Microsoft formatted versions of the laboratory normal data used previously in this chapter. The examples here are based on the capabilities found in Base SAS and SAS/ACCESS for PC Files in SAS 9.1. In Microsoft Excel, the lab normal data file might look like the following  

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. 

Beginning with SAS 9.1, the LIBNAME statement can be used to simply map to an Excel or Access database. This facility is available in the Microsoft Windows and UNIX operating systems. For example, the following SAS code reads in and then prints the lab normal file normal ranges.xls. 

---

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... 

Be aware that the LIBNAME statement approach allows for both reading and writing to and from Microsoft Office files, which means the contents of the Microsoft Office files can be changed by SAS. There are many libref options not covered here that provide further access control features to Microsoft Excel and Access files. See the SAS documentation on the LIBNAME statement for PC files for more details. 

Once the raw clinical data have been imported into SAS, the next step is to transform those raw data into more useful analysis-ready data. Raw data here mean data that have been imported without manipulation into SAS from another data source. That data source is likely to be a clinical data management system, but it could also be external laboratory data, IVRS data, data found in Microsoft Office files, or CDISC model data serving as the raw data. These raw data as they exist are often not ready for analysis. There may be additional variables that need to be defined, and the data may not be structured in a way that is required for a particular SAS analysis procedure. So once the raw data have been brought into SAS, they usually require some kind of transformation into analysis-ready files, which this chapter will discuss. 

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. 

You may find that you need to export your SAS data as something other than regular ASCII text or Microsoft Office files. In this case, the export wizards in the SAS windowing environment in SAS 9 and SAS Enterprise Guide 3.0 can easily export the following file formats ... 

There are other ways to convert a single piece of SAS output to PDF. You can use a free script found on the Internet, such as ascii2pdf, or purchase a PDF file converter from a third-party software vendor. If you have Acrobat Distiller with Acrobat Version 5, you can use the PDF distiller macros in Microsoft Office to print a single file from Microsoft Word to PDF. 

Figure 7.3 displays a Microsoft Excel spreadsheet containing the formulas and data for an LP transportation problem. This spreadsheet is one of six optimization examples included with Microsoft Excel 97. With a standard installation of Microsoft Office, the Excel workbook containing all six examples is in the file... 

This resume-creation software package is available for purchase online only. Web Resume Writer is compatible with Windows 95/ 98/NT/2000/XP and is designed to make producing printed or electronic resumes easier. When using this program, you will have total control over your resume s layout, appearance, and structure. The software is powerful, yet its point-and-click interface is designed for people who aren t too computer literate. To help ensure your resume has the most impact, a spell checker and the ability to ultimately export your resume file into Microsoft Office applications (including Word) is provided. 

The standard installation of Microsoft Office does nol include two extra items the Analysis Tool Pack , and the Frontline Systems SOLVER macro. Since the GT Calculator files require complex arithmetic, the Analysis Tool Pack musl be present. Since the EXCEL Hiickel and Extended Hiickel programmes depend on optimization as required by the application of the variation principle lo flic LCAO-MO Hamiltonian, the SOLVER macro, also, is needed. Both can be added to an existing installation of the OFFICE software using the Add-ins option in the TOOLS menu. 

As with other programs, Microsoft Office has a S ETU P. EXE file that it uses to start its installation routine. To start the install, simply insert the Office 2000 disk into your disk drive. Setup will begin automatically and will install Windows Installer onto your machine. If you already have a current version of the MS Installer software, the install continues, as in Figure 15.7. If not, an update will occur, and you may need to reboot and restart the install. 

Microsoft Office is able to repair itself as well because it uses the Windows Installer (as does Internet Explorer). If Office or Internet Explorer has a problem, the Installer is able to review the installation and either download the needed replacement files or ask the user to install a CD with the files on it, as in Figure 15.12. 

Because Solver is such a generally useful tool, it may be desirable to call it from inside a macro. This can be especially helpful in iterative procedures, where Solver must be called repeatedly. In the more recent versions of Excel this is indeed possible. It requires that you open your spreadsheet, select the VBA editor (with Alt + Fll), and use Tools => References. In the resulting References - VBAProject dialog box, under Available References, find and activate SOLVER.xls. To do so, click on Browse, select Files of type Microsoft Excel Files (. xls,. xla) and, in Look in , find where Solver is located. For example, you may have to double-click on SystemdiskfC ), Program Files, Microsoft Office, Office, Library, and Solver. In the File name window type Solver.xla, and Open. 

Calculations for design of stirred reactor for manufacturing of 25,000 metric tonnes per year of cyclohexylamine by hydrogenation of aniline are presented in a separate Excel file. This file works in aU Excel versions of Microsoft Office Excel 97-2003 and higher. The procedure is as follows ... 

The ELBT software works on IBM -compatible PC under Microsoft Windows 98 or later. It requires a CD-ROM drive, a minimum of 32 MB RAM, 60 MB available hard disk space, and Adobe Reader version 4.0 or later. Microsoft Office Excel 2002 or later is requested to view the XML files. 

The files can be saved in a user-defined directory. The default names are LBnnnn S.XML for the SELF display of the primary data, LBnnnn E.XML for the ELDATA display of the primary data, LBnnnnVLC.XML, for the calculated vapor-liquid equilibrium data, and LBnnnnGEC.XML for the calculated excess Gibbs energy data. The XML files can be viewed by means of Microsoft Office Excel 2002 or later. An example is shown in Fig. 3.22. 

A useful aspect of many software products is the ability to share information among programs. In this section, we will illustrate how Mathcad can exchange information with other software, focusing on Microsoft Excel and Word. While Microsoft Office is not designed as a mathematical tool, it is used commonly to write reports. The user may receive information in Word and want to transfer it into Mathcad or vice versa. Please note that for Mathcad 14, the user will need to save Excel files in Excel 2003 (.xls). Mathcad 15 supports the new Excel file format (.xlsx). 

For the Microsoft Excel spreadsheets, a file format from an earlier version of Excel before Microsoft Office 97 was used, if possible. This was done to provide maximum import capability for Excel users since the more recent versions can read earlier versions. Unfortunately, some of the features used were not supported by the earlier Excel versions, necessitating moving forward to a more recent version. Thus, the actual file version might vary from example to example depending on features supported. 

In order to install the demo you will need the Microsoft Installer, which is normally supplied with Windows ME, 2000 or XP. Is also comes with other Microsoft packages such as Office. If your PC does not have this installer you need to download it from Microsoft and install it first. Please refer to the file for more information. 

---