ACCESS Procedure Reference

OCCURS Statement

Modifies the occurrences of a repeating data field.

Optional statement
Applies to:	access descriptor or view descriptor

Syntax
Details

Syntax

OCCURS <">column-identifier<">
CONTENT <">occurrence-1<"> <=> SAS-date-format | length
<... <">occurrence-n<"><=> SAS-date-format| length>;
|
DROP occurrence <<TO> ... occurrence-n>;
|
FORMAT <">occurrence-1<"> <=> SAS-format-name
<... <">occurrence-n<"> <=> SAS-format-name>;
|
INFORMAT <">occurrence-1<"> <=> SAS-format-name
<... <">occurrence-n<"> <=> SAS-format-name>;
|
RENAME <">occurrence-1<"> <=> SAS-name
< ... <">occurrence-n<"> <=> SAS-name>;
|
RESET occurrence-1 <<TO> ... occurrence-n>;
|
SELECT occurrence <<TO> ... occurrence-n>;

Details

You use the OCCURS statement to modify values for occurrences of a repeating data field. The OCCURS statement can be used when creating an access descriptor or a view descriptor.

The OCCURS statement enables you to complete the following tasks:

select individual occurrences or a range of occurrences
drop individual occurrences or a range of occurrences
reset individual occurrences or a range of occurrences
change the format value for one or more occurrences
change the informat value for one or more occurrences
change the database content value for one or more occurrences
rename the SAS column name for one or more occurrences.

You can see the two-character numeric level of a CA-Datacom/DB field by using one of the LIST statements. The LVL column displays the word KEY for keys, the number 01 for simple fields and top-level compound fields, 02 for secondary fields, and so on. This listing is for information only and cannot be edited.

The column-identifier must be a repeating field, and can be the CA-Datacom/DB field name or the positional equivalent from the LIST statement. The occurrence argument can be the occurrence name or the occurrence number. If the CA-Datacom/DB field name or the occurrence name contains special characters, like '-' , enclose the name in quotation marks. The '=' is optional for all subcommands.

You can use the LISTOCC statement to review your changes.

You do not have to issue a SELECT statement in a view descriptor for occurrences included in the CONTENT, FORMAT, INFORMAT, and RENAME subcommands. The subcommands select the columns.

The OCCURS statement can take one of the following subcommands:

CONTENT

enables you to change the DB content attribute of an individual occurrence. This subcommand can be used when creating access or view descriptors. Changing the DB content attribute of an occurrence has the same effect on the SAS formats and informats for CA-Datacom/DB tables and records as changing the DB content attribute of a column. See CONTENT Statement for more information. For example, if the FIRSTORDERDATE column in the CUSTOMERS table is a repeating field, and you want to change the DB content attribute for occurrences nine and ten, submit the following statement:

   occurs firstorderdate content 9 yymmdd6.  10 = yymmdd6.;

DROP

enables you to drop individual occurrences from your descriptor. If you drop all occurrences of a column, the column is automatically dropped. This subcommand is used only when defining access descriptors.

You can drop one or more individual occurrences or a range of occurrences. For example, if you want to drop occurrences one, two, and three of the BRANCHOFFICE column in the CUSTOMERS table, submit the following statement:

   occurs "BRANCHOFFICE" drop 1 2 3;

                   or

   occurs "BRANCHOFFICE" drop 1 to 3;

FORMAT

enables you to change the format attribute of individual occurrences. This subcommand can be used when creating access or view descriptors. However, the format attribute cannot be changed in a view descriptor when you set ASSIGN=YES.

You can change the format attribute of one or more occurrences in one FORMAT subcommand. For example, if you want to change the format attribute for occurrences nine and ten of the BRANCHOFFICE column in the CUSTOMER table, submit the following statement:

   occurs "BRANCHOFFICE" format 9 $21.  10 = $8.;

INFORMAT

enables you to change the informat attribute of an individual occurrence. This subcommand can be used when creating access or view descriptors. However, the informat attribute cannot be changed in a view descriptor when you set ASSIGN=YES.

You can change the informat attribute of one or more occurrences in one INFORMAT subcommand. For example, if the BRANCHOFFICE column in the CUSTOMERS table is a repeating field, and you want to change the informat attribute for occurrences nine and ten, submit the following statement:

   occurs "BRANCHOFFICE" informat 9 $21. 10 = $8.;

RENAME

enables you to rename a SAS column name for an individual occurrence. This subcommand can be used when creating an access or view descriptor. However, this subcommand has different effects on access and view descriptors based on the value specified in the ASSIGN statement.

If you set ASSIGN=NO in the access descriptor, the SAS column name can be renamed. If you set ASSIGN=YES, the SAS column name can be renamed in the access descriptor but not in the view descriptor.

You can rename the SAS column name for one or more occurrences in one RENAME subcommand. For example, if you want to rename occurrences nine and ten of the BRANCH-OFFICE column in the CUSTOMERS table, submit the following statement:

   occurs "BRANCH-OFFICE" rename 9 london 10 = tokyo;

RESET

enables you to reset the attributes of individual occurrences. This subcommand can be used when creating an access or view descriptor. Specifying the RESET subcommand for an occurrence has the same effect on occurrence attributes as specifying the RESET statement for a column. See RESET Statement for more information.

You can reset one or more individual occurrences or a range of occurrences. For example, if you want to reset occurrences one, two, and three of the BRANCHOFFICE column in the CUSTOMERS table, submit the following statement:

   occurs "BRANCHOFFICE" reset 1 2 3;

                   or

   occurs "BRANCHOFFICE" reset 1 to 3;

SELECT

enables you to select individual occurrences to be included in your descriptor. This subcommand is used only when defining view descriptors.

You can select one or more individual occurrences or a range of occurrences. For example, if you want to select occurrences one, two, and three of the BRANCHOFFICE column in the CUSTOMERS table, submit the following statement:

   occurs "BRANCHOFFICE" select 1 2 3;

                   or

   occurs "BRANCHOFFICE" select 1 to 3;

The result of selecting a key that consists of multiple fields is always a SAS character column. The value of the SAS column is the concatenated values of the component fields. If any of the component fields are numeric, they are converted to character representation, with a format and length set by the interface view engine. The character-only restriction exists so that the key can be used in a WHERE clause.

Top of Page