Using a Format File to Register a Fixed-Width External File

Problem

You want to use a fixed-width external file in a SAS Data Integration Studio job. You also want to minimize the amount of column metadata that you must manually specify for the external file.

Solution

Create an external format file that specifies the column metadata for the external data file. In SAS Data Integration Studio, run the fixed-width external file wizard and specify both the data file and the format file. The wizard uses the format file to register the column metadata for the data file. This reduces the need to manually specify column metadata for the data file.

An external format file describes the structure of the columns in an external data file. The format file must be a well-formed file that the SAS INFILE statement can read.

For example, the following portion of a format file for a fixed-width data file contains census data. The format file is in comma-separated-values (CSV) format.

Name,SASColumnType,BeginPosition,EndPosition,ReadFlag,Desc,SASFormat,SASInformat
RECTYPE,C,1,1,y,Record Type,$char.,$char.
SERIALNO,C,2,8,y,Serial #: Housing Unit ID,$char.,$char.
SAMPLE,C,9,9,y,Sample Identifier,,
DIVISION,C,10,10,y,Division code,,
STATE,C,11,12,y,State Code,,
PUMA,C,13,17,y,Public Use Microdata Area (State Dpndnt),,
AREATYPE,C,18,19,y,Area Type Rev. for PUMS Equavalency fl,,
MSAPMSA,C,20,23,y,MSA/PMSA,,
PSA,C,24,26,y,PLANNING SRVC AREA (ELDERLY SAMPLE ONLY),,
SUBSAMPL,C,27,28,y,SUBSAMPLE NUMBER (USE TO PULL EXTRACTS),,
HOUSWGT,N,29,32,y,Housing Weight,,
PERSONS,N,33,34,y,Number of person records this house,,
...

The values in the first row are SAS column attributes. The values of subsequent rows specify metadata for the columns in the external file, in this case a fixed-width file that contains census data. Here is a description of the SAS column attributes in the first row.

Name

A logical identifier for the object, in this case a column name, such as RECTYPE and SERIALNO.

SASColumnType

This represents the SAS type (character or numeric) for this column. The value can be either 'C' or 'N'.

BeginPosition

The position within a record where the column begins. This is used for external tables and record-oriented tables.

EndPosition

The position within the record where the column ends. This is used for external tables and record-oriented tables.

ReadFlag

Indicates whether to read the column. If set to N, the column is ignored when the data is read in.

Desc

Brief description of the object, in this case a column.

For a full description of SAS column attributes, see the topics for the Column type and the Logical Column type in the SAS Metadata Model: Reference. The version of this book for SAS 9.3 applies to both SAS 9.3 releases and SAS 9.4 releases. This book can be accessed from the “Documentation by Title” section of the SAS Product Documentation page: http://support.sas.com/documentation/.

Note: If your external format file does not specify SAS informats for all column variables, you need to specify these manually in SAS Data Integration Studio. If appropriate informats are not provided for all columns, then incorrect results can be encountered when the external file is used in a job or when its data is viewed.

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:

Create an external format file that specifies the column metadata for the external data file. For more information about this file, see the “Solution” section above.
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 Import. The Import Column Definitions dialog box is displayed.
Select the Get the column definitions from a format file radio button.
Specify the path to the external format file that you created in Step 1.
Click OK. The column metadata in the external format file is applied to the current data file, as shown in the next display.
If your external format file did not specify SAS informats for all column variables, specify those now. Access the Informats column for each data column and select an appropriate SAS informat, as shown in the next display.
If you want to see what the data in the external file looks like with the column metadata applied, click Data tab, then click Refresh. Your data will be formatted with the current metadata. If the data looks correctly formatted, go to the next step. If the data does not look correctly formatted, then use the controls on the Column Definitions tab to correct the metadata.
Click Next and Finish to save the metadata and exit the fixed-width external file wizard.

View the External File Metadata

Follow the steps that are described in View the External File Metadata.

View the Data

Follow the steps that are described in View the Data.