The ACCESS Procedure for PC Files

CREATE Statement

Creates a SAS/ACCESS descriptor file.

Requirement:	This statement is required.
Note:	for DBF, DIF, WK1, WK3, WK4, Excel 4, Excel 5, and Excel 95 file formats under Windows operating environments access descriptor or view descriptor

Syntax

Details

Overview

Creating Access Descriptors

Creating View Descriptors

Examples

Example 1: Create an Access Descriptor for a Worksheet File

Example 2: Create an Access Descriptor for a Microsoft Excel Worksheet

Example 3: Create a View Descriptor from an Access Descriptor

Syntax

CREATE libref.descriptor-name. ACCESS | VIEW

Details

Overview

Use CREATE to create an access or view descriptor for a PC file that you want to access from SAS. To access a particular PC file of a supported type, you must create first an access descriptor, and then one or more view descriptors based on the access descriptor.

The descriptor name has three parts, separated by periods. The libref identifies a SAS library, which is associated with a directory on the local system disk where the descriptor is created. The libref must have been created already using the LIBNAME statement. The descriptor-name is the name of the descriptor to be created. The third part is the descriptor type. Specify ACCESS for an access descriptor or VIEW for a view descriptor.

You can use the CREATE statement as many times as necessary in one procedure execution. That is, you can create multiple access descriptors, as well as one or more view descriptors based on these access descriptors, within the same execution of the ACCESS procedure. Or, you can create access descriptors and view descriptors in separate executions of the procedure.

You can use the CREATE and the UPDATE in the same PROC ACCESS block with one restriction: a CREATE statement for a view descriptor should not follow an UPDATE statement.

Creating Access Descriptors

When you create an access descriptor, you must place statements or groups of statements in a certain order after the PROC ACCESS statement and its options, as listed here:

CREATE must be the first statement after the PROC ACCESS statement with one exception: if the block includes both the CREATE and the UPDATE statements, either statement can be the first in the block.
Specify any database-description statement, such as PATH=. This information describes the location and characteristics of the PC file. These statements must be placed before any editing statements. Do not specify these statements when you create view descriptors.

Information from database-description statements is stored in an access descriptor. Therefore, you do not repeat this information when you create view descriptors.
Specify any editing statements: ASSIGN, DROP, FORMAT, LIST, RENAME, RESET, and SUBSET. QUIT is also an editing statement, but using it terminates PROC ACCESS without creating your descriptor.
Specify the RUN statement. RUN executes the ACCESS procedure.

The order of the statements within the database-description and editing groups sometimes matters; see the individual statement descriptions for more information.

Note: Altering a PC file that has descriptor files defined on it might cause the descriptor files to be out-of-date or invalid. If you recreate a file and add a new column to the file, an existing access descriptor defined does not show that column, but the descriptor can still be valid. If you recreate a file and delete an existing column from the file, the descriptor is invalid. If the deleted column is included in a view descriptor that is used in a SAS program, the program fails and an error message is written to the SAS log.

Creating View Descriptors

You can create view descriptors and access descriptors in the same ACCESS procedure or in separate procedures.

To create a view descriptor and the access descriptor on which it is based within the same PROC ACCESS execution, place the statements or groups of statements in the order as follows:

Create the access descriptor as described in Creating Access Descriptors, except omit the RUN statement.
Specify the CREATE statement for the view descriptor. The CREATE statement must follow the PROC ACCESS statements that you used to create the access descriptor.
Specify any editing statements: SELECT, SUBSET, and UNIQUE are valid only when creating view descriptors. FORMAT, LIST, RENAME, and RESET are valid for both view and access descriptors. You can specify FORMAT, RENAME, and UNIQUE only when you specify ASSIGN= NO in the access descriptor that this view descriptor references. QUIT is also an editing statement. However, if you use it, it terminates PROC ACCESS without creating your descriptor.

Statement order within this group usually does not matter. See the individual statement descriptions for any restrictions.
Specify the RUN statement. RUN executes PROC ACCESS.

To create a view descriptor based on an access descriptor created in a separate PROC ACCESS step, specify the name in the ACCDESC= option. Specify the CREATE statement before any of the editing statements for the view descriptor.

If you create only one descriptor in a PROC step, the CREATE statement and any statements are checked for errors when you submit PROC ACCESS. If you create multiple descriptors in the same procedure, each CREATE statement and its statements are also checked for errors

If no errors are found when the RUN statement is processed, all descriptors are saved. If errors are found, they are written to the SAS log, and processing is terminated.

After you correct the errors, resubmit your statements.

Examples

Example 1: Create an Access Descriptor for a Worksheet File

LIBNAME adlib 'c:\sasdata';

PROC ACCESS DBMS=WK4;
   CREATE adlib.product.access;
   PATH='c:\sasdemo\specprod.wk4';
   GETNAMES=yes;
   ASSIGN=yes;
   RENAME= productid prodid
          fibername fiber;
   FORMAT productid  4.
          weight     e16.9
          fibersize  e20.13
          width      e16.9;
RUN;

Example 2: Create an Access Descriptor for a Microsoft Excel Worksheet

This example creates an access descriptor named AdLib.Employ for the Excel worksheet named C:\DUBOIS\EMPLOY.XLS. It also creates a view descriptor named VLib.Emp1204 for this same file:

LIBNAME adlib 'c:\sasdata';
LIBNAME vlib 'c:\sasviews';

PROC ACCESS DBMS=XLS;
   /* create access descriptor  */
   CREATE adlib.employ.access;
   PATH='c:\dubois\employ.xls';
   GETNAMES=yes;
   ASSIGN=no;
   LIST all;

   CREATE vlib.emp1204.view;
   /* create view descriptor  */
   SELECT empid lastname hiredate salary
          dept gender birthdate;
   FORMAT empid 6.
          salary dollar12.2
          jobcode 5.
          hiredate datetime7.
          birthdate datetime7.;
   SUBSET WHERE jobcode=1204;
RUN;

Example 3: Create a View Descriptor from an Access Descriptor

This example creates a view descriptor VLib.BDays from the AdLib.Employ access descriptor. It was created in the previous PROC ACCESS step. You could also use FORMAT because the access descriptor was created with ASSIGN= NO.

LIBNAME adlib 'c:\sasdata';
LIBNAME vlib 'c:\sasviews';

PROC ACCESS ACCDESC=adlib.employ;
   CREATE vlib.bdays.view;
   SELECT empid lastname birthdate;
   FORMAT empid 6.
          birthdate datetime7.;
RUN;