Import Wizard

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

SAS provides many ways of importing ASCII text files. These methods include using PROC IMPORT and the Import Wizard, the SAS DATA step, and SAS Enterprise Guide. 

The SAS IMPORT procedure (PROC IMPORT) provides a quick way to read an ASCII text file into SAS. You can call PROC IMPORT by typing in the SAS code, or you can use the convenient SAS Import Wizard to build the PROC IMPORT code for you. Let s start by looking at using the SAS Import Wizard to import the following pipe-delimited (using the character ) laboratory normal range reference file ... 

H3) Output - [untitled] r Log Unt1thd) g] Editor Untitledl S Import Wizard So... 

A SAS library can be selected from the drop-down list. For now, you will leave this as the WORK area and the member data set name will be set to LABNORM. When you click Next, a dialog box appears asking if you would like the PROC IMPORT procedure code to be saved. Saving is useful if you want to run the import process again without rerunning the Import Wizard. 

Output (Untitled) r] Log Untitied) Editor Untitledl Import Wizard - Cr... 

Now look at rows 21-24. Notice how ALKALINE PHOSPHATASE is truncated to ALKALINE PHOSP. This happens because the default behavior of the Import Wizard, PROC IMPORT, and the External File Interface (EFI) is that they scan only 20 rows deep into the file to determine variable attributes. Text field truncation is a common problem here. Another problem is that if a field appears to be numeric in the first 20 rows but later has character text beyond the scanning depth of PROC IMPORT, the procedure will terminate with an error message. There are two workarounds for this scanning depth problem. 

Program 3.4 PROC IMPORT Code Written by the Import Wizard to Read an ASCII File... 

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 interactive SAS Import Wizard provides an easy way to import the contents of Microsoft Excel and Access files into SAS. Here again, the Import Wizard is essentially a graphical user interface that builds the PROC IMPORT code for you. Begin in the interactive SAS windowing environment by selecting File from the toolbar and then Import Data... from the drop-down menu. A window like the following will appear, where you can select Microsoft Excel as a standard data source. 

Click Next and SAS will prompt you to see if you want to save the PROC IMPORT code generated by the Import Wizard. Click Finish to complete the file import. Here is the PROC IMPORT code generated by SAS from this run. 

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. 

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

If you use the Open command in Excel s File menu to open a text file. Excel will display the Text Import Wizard to allow you to parse the data. Either Delimited or Fixed Width text files can be parsed. The Fixed Width option is useful for columnar data separated by spaces, as illustrated in Figure 7-2. 

Figure 7-3. The Text Import Wizard Step 1 dialog box with preview of data to be parsed.

The Text Import Wizard usually recognizes whether the text file is Delimited or Fixed Width and displays the choice in the Step 1 dialog box, shown in Figure 7-3. You can override Excel s choice and manually select either the Delimited or the Fixed Width option. 

Figure 7-5. The Text Import Wizard Step 2 dialog box showing ruler and columnbreak delimiters.

The Text to Columns dialog boxes are essentially identical to those of the Text Import Wizard (see Figure 7-8). The data to be parsed must be in a single column. 

The Step 3 dialog box contains an input box (not present in the Text Import Wizard) that allows you to specify where you want the parsed data to be placed. The default option overwrites the original data column. 

Usually (though not for our exercise data files), importing data into the spreadsheet will involve the Text Import Wizard. This asks you about the nature of the data file (e.g., whether and how the data are delimited, i.e., how the various data points are separated from each other) and then helps you along. But that is beyond what we need to learn now it may become relevant if you want to import a long file with experimental data from some instrument. 

Select the data file SilagepH and follow the instructions of the Text Import Wizard. The data are comma- delimited, therefore use Delimited, and as delimiter specify a Comma. The data should appear in your spreadsheet after you use the Finish key. 

---