Establishing Connectivity to a Flat File

The following figure provides a logical view of using an external file as a data source.

Establishing Connectivity to External Files

You can connect to a flat file using the External File Source Designer in SAS Data Integration Studio.

Assume that the SAS software has already been loaded by using the standard installation wizard, and that the flat file is stored in a location that can be accessed. This example focuses on a comma-delimited flat file. A similar process is used for other types of flat files, but some steps are different.

To establish a connection to a flat file, perform the following steps:

Open SAS Data Integration Studio. Then, select FileNewExternal FileDelimited to access the New Delimited External File wizard.
Enter a name for the external file and click Next.
Enter the fully qualified path to the file in the File name field (for example, SAS-config-dir\sources\customer_data.dat). Click Next.
On the Delimiters and Parameters page of the wizard, deselect the Blank option in the Delimiters group box. Then, select the Comma option. Click Next to access the Column Definitions page of the wizard.
To define the columns, perform the following steps:
1. Click Refresh to view the data from the flat file in the File tab in the view pane at the bottom of the page.
2. Click Auto Fill to access the Auto Fill Columns dialog box. Change the value entered in the Start record field in the Guessing records group box to 2. This setting is based on the assumption that the first data record of the flat file contains header information and that the record is unique because it holds the column names for the file. Therefore, excluding the first data record from the guessing process yields more accurate preliminary data because it is excluded when the guessing algorithm is run.
Click Import to access the Import Column Definitions dialog box. The following four methods are provided for importing column definitions:
- Get the column definitions from other existing tables or external files.
- Get the column definitions from a format file.
- Get column definitions from a COBOL format file.
- Get the column names from column headings in the file.
In most cases, you get the column definitions from an external format file or from the column headings in the external file. Here is an example of a format file:
```
# Header follows
Name,SASColumnType,SASColumnName,SASColumnLength,SASInformat,SASFormat,Desc,ReadFlag
# Column definition records records follow
Make,C,Make,13,,$char13.,Manufacturer name column,y
Model,C,Model,40,,$char40.,Model name column,y
# Comma within quotation marks below is not a delimiter
Description,C,Description,32,$char32.,,'Description, Comments, Remarks',y
```
A sample of the output is shown in the following figure:

For this example, select the Get the column names from column headings in the file radio button. Keep the default settings for the fields underneath it.

Note: If you select Get the column names from column headings in the file, the value in the Starting record field in the Data tab of the view pane in the Column Definitions dialog box is automatically changed. The new value is one greater than the value in the The column headings are in file record field in the Import Column Definitions dialog box.
Click OK to return to the Column Definitions page.
The preliminary data for the external file object is displayed in the columns table at the top of the page. The Informat and Format columns for the rows in the table are based on the values that are included in the sample data that is processed by the guessing function. The results are accurate for this particular set of records, but you should still examine them to make sure that they are representative of the data in the rest of the flat file. Edit the values by clicking directly on the cells in the column table and making the necessary changes.
Click the Data tab at the bottom of the Column Definitions page. Then, click Refresh. The data should be properly formatted. If not, edit the cells in the column table and check the results by refreshing the Data tab. You can repeat this process until you are satisfied. You can review the SAS log for more details.

Note: 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.
Click Next.
Examine the final page of the wizard to ensure that the proper values have been entered. Click Finish to save the library settings. The file is ready for use.