Registering a Fixed-Width External File

Problem

You want to use a fixed-width external file in a SAS Data Integration Studio job.

Solution

Use the fixed-width external file wizard to register the file. The wizard enables you to create metadata for external files that contain fixed-width data. The metadata is saved to a SAS Metadata Server, where it can be accessed by SAS Data Integration Studio.

You need to know the width of each column in the external file. This information might be provided in a document that describes the structure of the external file.

Tasks

Run the Fixed-Width External File Wizard

Perform the following steps to use one method to register an external file in the fixed-width external file wizard:

Right-click the destination folder for the external file metadata. Then, select NewExternal FileFixed Width to access the General page in the New Fixed Width External File wizard. Enter an appropriate name and description of the external file that you want to register. Click Next to access the External File Location page.
If you are prompted, enter the user ID and password for the default SAS Application Server that is used to access the external file.
Specify the physical path to the external file in the File name field. Click Next to access the Parameters page.
The Pad column values with blanks check box is selected by default. Deselect this check box if the columns in your external file are short. It is unnecessary to pad values in short columns, and padded values can hurt performance. In addition, select the Treat unassigned values as missing check box. This setting adds the TRUNCOVER option to the SAS code, which sets variables without assigned values to missing.
Accept the default for the Logical record length, and click the Next button to access the Column Definitions page.
Click Refresh to view the raw data from the external file on the File tab in the view pane at the bottom of the page. Sample data is shown in the following display.

Sample Fixed-Width Data on the File Tab
Click the appropriate tick marks in the ruler displayed at the top of the view pane. You can get the appropriate tick mark position numbers from the documentation that comes with the data to set the boundaries of the columns in the external file. The process is similar to the process that is used to set tabs in word processing programs. To set the first column boundary, click the tick mark on the ruler that immediately follows the end of its data. A break line displays, and the column is highlighted. For example, if the data in the first column extends to the eighth tick mark, you should click the ninth mark. Notice that the metadata for the column is also populated into the column component at the top of the page.
Click the appropriate tick marks in the ruler for the other columns in the external file. Break lines and metadata for these columns are set.
Click Auto Fill to refine this preliminary data by using the auto fill function. Accept all default settings and then click OK to return to the Column Definitions page. More accurate metadata is entered into the column components section of the page.
The preliminary metadata that is populated into the columns component usually includes column names and descriptions that are too generic to be useful for SAS Data Integration Studio jobs. Fortunately, you can modify the columns component by clicking in the cells that you need to change and by entering the correct data.

Note: The only values that need to be entered for the sample file are appropriate names and descriptions for the columns in the table. The other values were created automatically when you defined the columns and clicked Auto Fill. However, you should make sure that all variables have informats that describe the data that you are importing because the auto fill function provides a best estimate of the data. You need to go in and verify this estimate. If appropriate informats are not provided for all variables in the fixed-width file, then incorrect results can be encountered when the external file is used in a job or when its data is viewed. A sample of a completed Column Definitions page is shown in the following display.

Sample Completed Column Definitions Page

You can click Data to see a formatted view of the external file data. To view the code that is generated for the external file, click the Source tab. To view the SAS log for the generated code, click the Log tab. The code that is displayed in the Source tab is the code that is generated for the current external file when it is included in a SAS Data Integration Studio job.
Click Next and Finish to save the metadata and exit the fixed-width external file wizard.

View the External File Metadata

After you have generated the metadata for an external file, you can use SAS Data Integration Studio to view, and possibly make changes to, that metadata. For example, you might want to remove a column from a table or change the data type of a column. Any changes that you make to this metadata do not affect the physical data in the external file. However, the changes affect the data that is displayed when the external table is used in SAS Data Integration Studio. Perform the following steps to view or update external file metadata:

Right-click the external file, and click Properties. Then, click the Columns tab. The Columns tab is displayed, as shown in the example in the following display.

Sample External File Columns Tab
Click OK to save any changes and close the properties window.

View the Data

Right-click the external file, and click Open as Table. The View Data window is displayed, as shown in the example in the following display.

Sample External File Data in the View Data Window

If the data in the external file displays correctly, the metadata for the file is correct and the table is available for use in SAS Data Integration Studio. If you need to review the original data for the file, right-click on its metadata object. Then, click Open.