Excel files

The source code is now in VisualBasic (it used to be in GW-BASIC, later in QBASIC) the files are provided in compiled form, together with a structured menu. Some Excel files are included in the XLS directory. 

Note-. In newer versions of BASIC, line numbers are no longer needed. Compare this BASIC code to Excel file ELECTRODE.xls to gain a feeling for the difference in approach necessary to obtain the same result on software platforms geared towards efficient and flexible programming, respectively user convenience. 

This chapter contains the algorithms necessary for approximating statistical tables, some program kernels in BASIC, instructions on how to install the VisualBasic programs, and finally, a description of each of the VB programs and the Excel files. 

Instructions) and (Import Data from Excel) allow a portion of an Excel spreadsheet to be copied into a SMAC-data file the necessary steps are as follows (1) open the Excel file, mark the range to be copied and press [Copy] (2) open DATA and create a new or open an existing data file (3) select (Input Data) (Import Data from Excel) and position the cursor on an appropriate cell, e.g. cell (1,1). The imported data replaces any data that was in that cell range. If the imported cell range needs more columns or rows than are presently available, the data array is correspondingly increased in size. Headers and dimensions can be added later. 

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. 

Note that the EXCEL engine specification is optional, because SAS would read the .xls extension in the physical filename and assume it indicates a Microsoft Excel file. Also note that the xlsfile libref refers to the entire Excel workbook. In the subsequent PROC PRINT, the normal ranges must be specified so SAS will know which Excel worksheet to read. The data set/worksheet name in the PROC PRINT looks odd because of the existence of a special character, which is normally not allowed as part of a data set name. 

Click Next, and a file browser window will open that allows for the drill-down and selection of the Microsoft Excel file of interest. Once the file is selected, a Select Table window will open. This window allows you to pick which worksheet in the Excel file you want to turn into a SAS data set. Click the Options button to see the new options available with SAS 9.1 and PROC IMPORT. 

Program 3.8 PROC IMPORT Code Generated by the Import Wizard to Read a Microsoft Excel File... 

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

From within a project in SAS Enterprise Guide, select File and then Import Data... from the toolbar. Select where to open the data from in the pop-up file selection window and click Open. SAS Enterprise Guide knows by default that you want to open an Excel file and provides a worksheet selection window that looks like this ... 

Next, simply select the normal ranges sheet and click Open to advance to the Import Data window. That window looks like the following for your Excel file ... 

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. 

After you click Next, the Connect to MS Excel dialog box appears, allowing you to specify the name of the destination Excel file as follows ... 

Click Next, and SAS offers to save the PROC EXPORT statements in a SAS program. Click Finish and your Excel file is created. The PROC EXPORT code SAS saved in this example follows ... 

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

Select Local Computer or SAS Servers from the pop-up dialog box. Then, select Microsoft Excel Files from the Save as type selection list in the Export window 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. 

A tab-delimited file is created from the Excel file. 

The data for genes with different expression as compared to negative control 1 (either up- or downregulated) are saved in an Excel file for each clinical subject (see also Fig. 17.1A) and sorted alphabetically. 

To read the exported CSV result files of CellProfiler into KNIME, we first capture the path of all files with a List Files node. The list of paths is then connected to an Iterate List of Files node to load the data into a KNIME workflow. The barcode, plate row, and plate column metadata contained in the CSV files are used to associate a plate layout file (either a CSV file or a Microsoft Excel file) to an experimental condition for each line (lines representing either objects or images). This association can be carried out either with a Joiner or a dedicated Join Layout node developed by us. We have generated tables containing over 10 million lines and hundreds of columns. KNIME is able to carry out computations... 

For the Excel files, the following lists are provided as different worksheets within one Excel file. 

To open and to view this csv file use GRID in PHREEQC. The species with the highest concentration (predominant species) have to be determined for each row (i.e. for each pE-pH combination). To avoid doing this manually, the data have to be copied into EXCEL and to be treated with a macro, which can also be found on the CD enclosed in this book. The macro can be activated by opening the Excel file macro.xls from the book s CD and by clicking on activate macros . Now, one can either copy data instead of the given test data into table 1 or open the. csv file directly in Excel. The activated macro is available for all open Excel files. The macro itself can be opened under menu Extras / Macro / Macros under the name... 

---