ACCESS Procedure Reference

CREATE Statement

Creates a SAS/ACCESS descriptor file.

Required statement
Applies to:	access descriptor or view descriptor

Syntax
Details
Access Descriptors
View Descriptors
Examples

Syntax

CREATE libref.member-name.ACCESS|VIEW;

Details

The CREATE statement identifies the access descriptor or view descriptor that you want to create. This statement is required for creating a descriptor.

To create a descriptor, use a three-level name. The first level identifies the libref of the SAS library where you will store the descriptor. You can store the descriptor in a temporary (WORK) or permanent SAS library. The second level is the descriptor's name (member name). The third level is the type of SAS file: 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.

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 below:

CREATE statement for the access descriptor: must follow the PROC ACCESS statement.
Database-description statements: must follow the CREATE statement. Use either the ADBFILE or the DDM statement with the SECFILE and SYSFILE statements. Additionally with the DDM statement, use the NSS statement. The ADBFILE statement enables you to access an ADABAS file. The DDM statement accesses a view to an ADABAS file that you can use to reference the ADABAS file in NATURAL programs. In making your choice, note that the two statements use different naming conventions for ADABAS data field names.

Information from database-description statements is stored in an access descriptor; therefore, you do not need to repeat this information when you create view descriptors. However, if no security values were entered in the access descriptor or values were provided but the SECURITY statement was set to NO, then you can use the database-description statements in a view descriptor to supply or modify them.
Editing statements: must follow the database-description statements. ASSIGN, CONTENT, DROP, EXTEND, FORMAT, INFORMAT, KEY, LIST, LISTINFO, LISTOCC, MVF, RENAME, RESET, and SECURITY can all be used in an access descriptor. QUIT is also an editing statement but using it terminates PROC ACCESS without creating your descriptor.
RUN statement: this statement is used to process the ACCESS procedure.

The order of the statements within the database-description group does not matter. For example, you could submit either the DDM= or the NSS() statement first. The order of the statements within the editing group sometimes matters; see the individual statement descriptions for any restrictions.

Note: Altering a DBMS table that has descriptor files defined on it might cause these files to be out-of-date or not valid. For example, if you re-create a table and add a new column to the table, an existing access descriptor defined on that table does not show that column; in this case the descriptor is still valid. However, if you re-create a table and delete an existing column from the table, the descriptor might not be valid. If the deleted column is included in a view descriptor and this view is used in a SAS program, the view fails and an error message is written to the SAS log. [cautionend]

View Descriptors

You can create view descriptors and access descriptors in the same execution of the ACCESS procedure or in separate executions.

To create a view descriptor and the access descriptor on which it is based within the same PROC ACCESS execution, you must place the statements or groups of statements in a particular order after the PROC ACCESS statement and its options, as listed below:

Create the access descriptor except omit the RUN statement.
CREATE statement for the view descriptor: this statement must follow the PROC ACCESS statements that created the access descriptor.
NSS and the password and cipher code parameters of ADBFILE, SECFILE, and SYSFILE: the ADBFILE, SECFILE, and SYSFILE statements can be specified only when SECURITY=NO or when SECURITY=YES and no values have been specified in the access descriptor referenced by this view descriptor.
Editing statements: SELECT and SUBSET are used only when creating view descriptors. CONTENT, FORMAT, INFORMAT, KEY, and MVF OCCURS can be specified only when ASSIGN=NO is specified in the access descriptor referenced by this view descriptor. QUIT is also an editing statement, but using it terminates PROC ACCESS without creating your descriptor.

The order of the statements within this group usually does not matter; see the individual statement descriptions for any restrictions.
RUN statement: this statement is used to process the ACCESS procedure.

To create a view descriptor based on an access descriptor that was created in a separate PROC ACCESS step, you specify the access descriptor's name in the ACCDESC= option in the new PROC ACCESS statement. You must 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 its accompanying statements are checked for errors when you submit PROC ACCESS for processing. If you create multiple descriptors in the same PROC step, each CREATE statement (and its accompanying statements) is checked for errors as it is processed.

When the RUN statement is processed, all descriptors are saved. If no errors are found, the descriptor is saved. If errors are found, error messages are written to the SAS log, and processing is terminated. After you correct the errors, resubmit your statements.

Examples

The following example creates the access descriptor ADLIB.CUSTOMER on the ADABAS CUSTOMER file using the ADBFILE statement to specify the ADABAS file.

/* Create access descriptor using ADABAS file */
proc access dbms=adabas;
   create adlib.customer.access;
   adbfile(number=45  password=cuspw   
           cipher=cuscc dbid=1);
   sysfile(number=15  password=cuspwsys  
           cipher=cusccsys  dbid=1);
   secfile(number=16  password=cuspwsec  
           cipher=cusccsec  dbid=1);
   assign=yes;
   rename cu = custnum
          ph = phone
          ad = street;
   format fo = date7.;
   informat fo = date7.;
   content fo = yymmdd8.;
   mvf br occurs = 4
run;

The following example creates an access descriptor to the same data using the DDM statement.

/* Create access descriptor using NATURAL DDM */
proc access dbms=adabas;
   create adlib.customer.access;
   nss(library=sasdemo user=demo password=demopw).
   sysfile(number=15  password=cuspwsys  
           cipher=cusccsys  dbid=1);
   secfile(number=16  password=cuspwsec  
           cipher=cusccsec  dbid=1);
   ddm=customers;
   assign=yes;
   rename customer = custnum
         telephone = phone
         streetaddress = street;
   format firstorderdate = date7.;
   informat firstorderdate = date7.;
   content firstorderdate = yymmdd6.;
   mvf "BRANCH-OFFICE" occurs = 4
run;

The following example creates an access descriptor ADLIB.EMPLOY on the ADABAS EMPLOYEES file and a view descriptor VLIB.EMP1204 based on ADLIB.EMPLOY in the same PROC ACCESS step. The ADABAS file to access is referenced by a DDM.

/* Create access and view descriptors in 
one execution */
proc access dbms=adabas;
   /* Create access descriptors */
   create adlib.employ.access;
   nss(library=sasdemo user=demo password=demopw);
   sysfile(number=15  password=cuspwsys  
           cipher=cusccsys  dbid=1);
   secfile(number=16  password=cuspwsec  
           cipher=cusccsec  dbid=1);
   ddm=employee;
   assign=no;
   list all;

   /* Create view descriptor  */
   create vlib.emp1204.view;     
   select empid lastname hiredate salary dept 
   sex    birthdate;
   format empid 6.
          salary dollar12.2
          jobcode 5.
          hiredate datetime7.
          birthdate datetime7.;
   subset where jobcode=1204;
run;

The following example creates a view descriptor VLIB.BDAYS from the ADLIB.EMPLOY access descriptor, which was created in a separate PROC ACCESS step.

/* Create view descriptors in separate execution */
proc access dbms=adabas accdesc=adlib.employ;
   create vlib.bdays.view;
   select empid lastname birthdate;
   format empid 6.
          birthdate datetime7.;
run;

Top of Page