Registering an External File with User-Written Code

Problem

You want to register an external file whose structure is more complex than can be easily managed in the delimited wizard or the fixed width wizard.

Solution

Use the New User-Written External File wizard to specify a user-written SAS INFILE statement to read the structure of the file. The wizard uses the INFILE statement to read the structure of the file, and then it registers the file on the metadata server. The metadata object for the file can then be used as a source or a target in a SAS Data Integration Studio job.

Tasks

Test Your Code

You should test your SAS code before you run it in the User Written External File wizard. That way, you can ensure that any problems that you encounter in the wizard come from the wizard itself and not from the code. Perform the following steps to test your code:

Open the Code Editor window from the Tools menu in the menu bar on the SAS Data Integration Studio desktop.

Paste the SAS code into the Code Editor window. Here is the code that is used in this example:

libname
temp base
'\\machine number\output_sas';
%let _output=temp.temp;
data &_output;

   infile '\\machine number\sources_external\birthday_event_data.txt'
          lrecl = 256
          pad
          firstobs = 2;
   
   attrib Birthday length = 8   format = ddmmyy10.  informat = YYMMDD8. ; 
   attrib Event    length = $19 format = $19.       informat = $19.     ;
   attrib Amount   length = 8   format = dollar10.2 informat = comma8.2 ; 
   attrib GrossAmt length = 8   format = Dollar12.2 informat = Comma12.2; 
   
   input  @ 1  Birthday YYMMDD8.
          @ 9  Event    $19.
          @ 28 Amount   Comma8.2
          @ 36 GrossAmt Comma12.2; 
   
run;

Note: The first two lines of this SAS code are entered to set the LIBNAME and output parameters that the SAS code needs to process the external file. After you have verified that the code ran successfully, delete the first two lines of code. They are not needed when the SAS code is used to process the external file.

Review the log in the Code Editor window to ensure that the code ran without errors. The expected number of records, variables, and observations should have been created.
Close the Code Editor window. Do not save the results.

Run the User-Written External File Wizard

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

Right-click the destination folder for the external file metadata. Then, select NewExternal FileUser Written to access the General page in the New Delimited External File wizard. Enter an appropriate name, description, and location of the external file that you want to register. Click Next to access the User Written Source Code 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.
Enter the appropriate value in the Type field. The available types are File and Metadata. For example, you can select File type to point to code that is embedded in a separate file. If you select Metadata, you must click Edit and paste the code in the Edit Source Code window.

Note: The Host and Path fields on the User Written Source Code page are displayed only when you select File in the Type field. Different fields are displayed when you select Metadata.
Verify that the correct server is displayed in the Host field.
Specify the physical path to the external file in the Path field. Click Next to access the Column Definitions window. For example, you can register the metadata for an external file that is named birthday_event_data.txt.
You can either enter the column definitions manually or click Import to access the Import Column Definitions window. For information about the column import functions available there, see the "Import Column Definitions Window" in the SAS Data Integration Studio Help. The column definitions for this example were entered manually.

You can find the information that you need to define the columns in the attributes list in the SAS code file. For example, the first variable in the birthday_event_code.sas file has a name of Birthday, a length of 8, the yymmdd8. informat, and the ddmmyy10. format. Click New to add a row to the columns component at the top of the Column Definitions window.
Review the data after you have defined all of the columns. To view this data, click the Data tab under the view pane at the bottom of the window. 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. The following display shows the completed Column Definitions window.

Completed Column Definitions Window
Click Next to access a summary page, and then click Finish to save the metadata and exit the user written 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 included 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.

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.

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.