Wizard Text Import

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

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. 

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. 

To transfer the R output to MS Excel, copy the first section of the table without the column headers (for example the PC1-PC8 data before the first section break) and paste it into an MS Word document. Change the font to Courier and the size to 5 pt such that the text in the document resembles a table. Save the file as a Text-only (.txt) file. From MS Excel, import the data using Data > Get External Data. Select the file, then choose the Fixed width option in Step 1 of the Text Import Wizard, click on Next , verify column divisions, and then click on Finish . 

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

---