SAS/ACCESS Interface to CA-DATACOM/DB: Reference

Creating SAS/ACCESS Descriptor Files

The examples in this section illustrate creating a permanent access descriptor named MYLIB.CUSTS and two view descriptors named VLIB.USACUSTS and VLIB.CUSTADD. Begin by using the SAS LIBNAME statement to associate librefs with the SAS data libraries in which you want to store the descriptors. (See the SAS documentation for your operating system for more details on the LIBNAME statement.)

You can have one library for access descriptors and a separate library for view descriptors, or you can put both access descriptors and view descriptors in the same library. Having separate libraries for access and view descriptors helps you maintain data security by enabling you to separately control who can read and update each type of descriptor.

In this book, the libref MYLIB is used for access descriptors and the libref VLIB is used for view descriptors.

The ACCESS Procedure

You define descriptor files with the ACCESS procedure. You can define access descriptor files and view descriptor files in the same procedure execution or in separate executions. Within an execution, you can define multiple descriptors of the same or different types.

The following section shows how to define an access descriptor and multiple view descriptors in a single procedure execution. Examples of how to create the same descriptor files in separate PROC ACCESS executions are provided in Data and Descriptors for the Examples.

When you use a separate PROC ACCESS execution to create a view descriptor, note that you must use the ACCDESC= option to specify an existing access descriptor from which the view descriptor will be created.

Creating Access Descriptors and View Descriptors in One PROC Step

Perhaps the most common way to use the ACCESS procedure statements is to create an access descriptor and one or more view descriptors based on this access descriptor in a single PROC ACCESS execution. The following example shows how to do this. First an access descriptor is created (MYLIB.CUSTS). Then two view descriptors are created (VLIB.USACUST and VLIB.CUSTADD). Each statement is then explained in the order that it appears in the example program.

proc access dbms=datacom;
 create mylib.custs.access;
  user=demo;  
  table=customers; 
  assign = yes; 
  drop contact;
  list all;
  extend all;
  rename customer = custnum telephone = phone 
         streetaddress = street;
  format firstorderdate = date7.;
  informat firstorderdate = date7.;
  content firstorderdate = yymmdd6.; 
  list all; 

 create vlib.usacust.view;   
  select customer state zipcode name 
         firstorderdate; 
  list view;
  extend view;

  subset where customer eq 1#;
  subset sort firstorderdate; 
  list view;  

 create vlib.custadd.view; 
  select state zipcode country name city; 
  list view; 

  list all; 

run;

proc access dbms=datacom;

invokes the ACCESS procedure for the SAS/ACCESS interface to CA-DATACOM/DB.

create mylib.custs.access;

identifies the access descriptor, MYLIB.CUSTS, that you want to create. The MYLIB libref must be associated with a SAS data library before you can specify this statement.

user=demo;

specifies a required CA-DATADICTIONARY userid. In this case, the user name is DEMO for the CA-DATACOM/DB table CUSTOMERS. The name is the 32-character entity-occurrence name of a PERSON entity in CA-DATADICTIONARY. The value entered is saved in the access descriptor and any view descriptor created from it. The user name and optional password (not used here) must have CA-DATADICTIONARY retrieval authority on six entity-types: DATABASE, FILE, RECORD, ELEMENT, KEY, and FIELD.

table=customers;

indicates the name of the CA-DATACOM/DB table that you want to use. The table name is required. The table name is a 32-character field that names an entity-occurrence of type RECORD in CA-DATADICTIONARY. (For CA-DATACOM/DB R8, the type is TABLE.) The combination of values in the TABLE statement and optional DATABASE and STATUS statements (not used here) must be unique.

assign = yes;

generates unique SAS column names based on the first eight non-blank characters of the CA-DATACOM/DB field names. The column names and attributes can be changed in this access descriptor but not in any view descriptors created from this access descriptor.

Note that although the ASSIGN statement assigns names to the columns, it does not select them for inclusion in any view descriptors created from this access descriptor. You must select the fields in the view descriptor with the SELECT statement. Unless fields are dropped, they are automatically included in the access descriptor.

drop contact;

marks the CA-DATACOM/DB field with the name CONTACT as non-display. The CONTACT field is a simple field; therefore, it is the only DBMS column that is dropped. When the DROP statement indicates a compound field, which can consist of multiple simple and compound fields, all DBMS columns associated with the compound field are marked as non-display, unless otherwise specified with the OCCURS statement. Compound fields are identified by the word *GROUP* in their description in the LIST statement output.

Columns that are dropped also do not appear in any view descriptors created from this access descriptor.

list all;

lists the access descriptor's item identifier numbers, the CA-DATACOM/DB field names, the CA-DATACOM/DB level numbers, the SAS column names, and the SAS formats. You can use the item identifer as a field identifier in statements that require you to use the DBMS column name. The list is written to the SAS log. Any columns that have been dropped from display (using the DROP statement) have *NON-DISPLAY* next to them.

extend all;

lists information about the SAS columns in the access descriptor, including the informat, the DB content, and the number of times a field repeats. The list is written to the SAS log. When you are creating multiple descriptors, you can use the EXTEND statement before the next CREATE statement to list all the information about the descriptor you are creating.

rename customer = custnum telephone = phone streetaddress = street;

renames the default SAS column names associated with the CUSTOMER, TELEPHONE, and STREETADDDRESS fields to CUSTNUM, PHONE, and STREET, respectively. Specify the CA-DATACOM/DB field name or its positional equivalent from the LIST statement on the left side of the equal sign (=) and the new SAS name on the right. Because the ASSIGN=YES statement is specified, any view descriptors created from this access descriptor will automatically use the new names.

format firstorderdate = date7.;

changes the FIRSTORD SAS column from its default format to a new SAS format. The format specifies the way a value will be printed, in this case, as a date format. Specify the CA-DATACOM/DB field name or its positional equivalent from the LIST statement on the left side of the equal sign (=) and the new SAS format on the right. Because the ASSIGN=YES statement is specified, any view descriptors created from this access descriptor will automatically use the new format for the FIRSTORD column.

informat firstorderdate = date7.;

changes the FIRSTORD SAS column from its default informat to a new SAS informat. The informat specifies the way a value will be read, in this case, as a date informat. Specify the CA-DATACOM/DB field name or its positional equivalent from the LIST statement on the left side of the equal sign (=) and the new informat on the right. Because the ASSIGN=YES statement is specified, any view descriptors created from this access descriptor will automatically use the new informat for the FIRSTORD column.

content firstorderdate = yymmdd6.;

specifies the SAS date format to use for the FIRSTORD SAS column. This format indicates the way date values are represented internally in the CA-DATACOM/DB table, in this case, yymmdd. Specify the CA-DATACOM/DB field name or its positional equivalent from the LIST statement on the left side of the equal sign (=) and the date format on the right. Because the ASSIGN=YES statement is specified, any view descriptors created from this access descriptor will automatically use this date format for the FIRSTORD column.

list all;

lists the item identifiers, the CA-DATACOM/DB field names, the SAS column names, and other SAS information in the access descriptor so you can see the modifications before proceeding with the next CREATE statement.

create vlib.usacust.view;

writes the access descriptor to the library associated with MYLIB and identifies the view descriptor, VLIB.USACUST, that you want to create. The VLIB libref must be associated with a libref before you can specify this statement.

select customer state zipcode name firstorderdate;

selects the CUSTOMER, STATE, ZIPCODE, NAME, and FIRSTORDERDATE fields for inclusion in the view descriptor. A SELECT statement is required to create the view, unless a RENAME, FORMAT, INFORMAT, or CONTENT statement is used.

list view;

lists the item identifiers, the DBMS column names, the SAS column names, and other SAS information associated with the CA-DATACOM/DB fields selected for the view. The list is written to the SAS log.

extend view;

lists detail information about the SAS columns in the view, including the informat, the DB content, and the number of times a field repeats. The list is written to the SAS log.

subset where customer eq 1#;

specifies you want to include only records with 1 as the first character in the CUSTOMER DBMS column.

subset sort firstorderdate;

specifies you want to order the records by the value of the FIRSTORDERDATE DBMS column.

list view;

lists the item identifiers, the DBMS column names, the SAS column names, and other SAS information associated with the view, to show the modifications.

create vlib.custadd.view;

writes view descriptor VLIB.USACUST to the library associated with VLIB and identifies a second view descriptor, VLIB.CUSTADD, that you want to create.

select state zipcode country name city;

selects the STATE, ZIPCODE, COUNTRY, NAME, and CITY fields for inclusion in the view descriptor.

list view;

lists the item identifiers, the DBMS column names, the SAS column names, and other SAS information associated with the CA-DATACOM/DB fields selected for the view.

list all;

lists updated SAS information for the fields in the access descriptor. Fields that were dropped have *NON-DISPLAY* next to the SAS column description. Fields selected in the VLIB.CUSTADD view descriptor have *SELECTED* next to them. Fields selected in VLIB.USACUST will not show as selected in the access descriptor. Selection information, including status and any selection criteria, are reset in the access descriptor for each new view descriptor. The list is written to the SAS log.

run;

writes the view descriptor when the RUN statement is processed.

Chapter Contents
Previous
Next
Top of Page