Practical Data Management and Statistical Computing (BioEp691F)

Contacts

Outline

Assignments

Resources

---

Outline: Lec11 Lec12 Lec13 lec14 Lec15 Lec16 Lec17 Lec18 Lec19 Lec20
Lectures: Lec11 Lec12 Lec13 Lec14 Lec15 Lec16 Lec17 Lec18 Lec19 Lec20

---

Lecture 16

It is possible in SAS to read data that have been stored by other data base systems. The advantage of reading data directly is that variable names, formats, and other data characteristics can be preserved with the data set. In order for this to occur, SAS needs to recognize the other DATA Base system, and be able to translate the variable definitions to SAS definitions.

We illustrate an application of this process using a DBASE data set created as part of a study on Carpal Tunnel Syndrome. The structure of the DBASE data is identified in an ACCESS file (*.sa2). A VIEW file (*.sv2) allows the data in DBASE to be examined without creating a SAS data set. The advantage is that no permanent SAS data set need be created. Data from the DBASE data set can be examined from the Library Icon.

SAS reads external data sets in steps.

• At the first step, a SAS "ACCESS" data set is created that contains information from the DBASE application necessary to recognize the variables and formats.
• At the second stage, a SAS "VIEW" file is created that enable data from the DBASE data set to be read.

The "VIEW" file does not read the DBASE data, but rather contains statements that specify how the DBASE data will be read when they are read. The program LEC16P4.SAS creates these file from the DBASE file Addnew5.dbf. We can create a SAS data file from the SAS VIEW as in LEC16P5.SAS.

DBASE files can also be read using the IMPORT wizzard. To do so, use the GLOBAL menu and select DESKTOP in SAS. Then choose the DATA ACCESS and Management ICON, and finally the IMPORT ICON.

Different options allow Excel or other spreadsheet files to be read into SAS. We illustrate a simple example here where the variable names are not included in the data set read from the Excel spreadsheet using LEC16P6.SAS. Using SAS 6.12, the Excel file must be saved in Version 5 (or lower), not Excel Version 7 (the most current version). We read an Excel (version 5) file called wtstudy1.xls into SAS.

The program creates and ACCESS data set (wtstudy1.sa2), and a VIEW data set (wtstudy1.sv2). We only need the VIEW data set to gain access to the data. Note that the VIEW data set includes information that points to the location of the Excel file.

To create a SAS data set from the VIEW, we use LEC16P7.SAS, which is listed in the output.

Reading:Access Descriptors- p206-207, SAS Language Guide, Version 6, 1st Edition.

DBMS Copy is software designed to translate data from one software format to another. This software is easy to use, and more likely to be up to date than individual software venders.

The Hampden County Tobacco Free Coalition is conducting a 5% postcard survey of registered voters in Western Massachusetts to assess attitudes towards smoke free restaurants. The survey covers 21 cities and towns in Western Massachusetts. Massachusetts has a law that requires a voter registration list to be constructed (and made available) each year, and the lists represent the population to be sampled. The election list from Springfield came to the coalition (through Dalila Gomes) on three 3.5" disks (each containing one compressed ASCII file in a pipe-delimited format). Instructions that came with the file explained that it could be read with MS ACCESS.

• The first step was to construct a subset of the pipe-delimited file, which was saved as a file called sptest1.txt. Although several attempts were made to read this file in using "|" as a delimiter in SAS, we were not successful. As a result, we followed the instructions and read the data file into ACCSS.
• To create the ACCESS file, we
  • Open MS access from the START menu, and create a blank data base called sptest.mdb.
  • go to FILE
    • Get external Data
      • Import and then locate the file sptest1.txt to import
      • Click on "delimited" for the file format, and then NEXT
      • In the OTHER box, type: |
      • Store data in a New Table
      • FINISH
• We then export the ACCESS file to an Excel file (version 4), so as to be able to read it into SAS. To export the file
  • Choose from the FILE menu: SaveAs/Export
    • Choose: to an external file or data base
    • Select MS Excel version 4.

The resulting Access file is called SPTEST1.mdb and the Excel file is called SPTEST1.xls, which can be read into SAS using the IMPORT wizzard. Note that the date formats were not transferred properly from the Access file to the Excel file.