Generating Unique Sequence Numbers as an Identifier Variable

Overview

Some of the domains in the CDISC SDTM data standard model require a sequence number as an identifier variable (–SEQ). This sequence number ensures the uniqueness of records for each subject within a data set.

For example, a study might have a series of vital signs collected during each visit. On some days, several readings were taken for heart rate and blood pressure. Before you can load study data into the SDTM VS domain, you need to uniquely identify each of these vital signs for each subject. This means that when you convert the study data, you need to populate each domain and populate the VSSEQ variable.

Use the Subject Sequence Generator transformation to generate a unique sequence number across subjects in a domain. After running the Subject Sequence Generator transformation, another variable is generated that enables you to uniquely identify each vital sign.

Note: For detailed information about creating a job with a transformation, see the SAS Data Integration Studio: User's Guide or the SAS Data Integration Studio online Help.

Generate Unique Sequence Numbers

To generate unique sequence numbers with the Subject Sequence Generator transformation, perform the following steps:

Select FileNewJob.

An empty job diagram appears on the Diagram page.
From the Folders tree, drag the source table for the domain onto the diagram.
In the Transformations tree, expand Clinical, and then drag Subject Sequence Generator onto the diagram.
Drag the cursor from the source table to the input port of the transformation.

This action connects the source to the Subject Sequence Generator transformation.
In the Transformations tree, expand the Access folder, and then drag Table Loader onto the diagram.
Drag the cursor from the output table to the input port of the Table Loader.

This action connects the Subject Sequence Generator_OUTPUT table to the Table Loader.
From the Folders tree, drag the SDTM VS domain that you want to populate onto the diagram.
Drag the cursor from the output port of the Table Loader to the input port of the SDTM VS domain.

This action connects the Table Loader to the SDTM VS domain (the data target).
Right-click Subject Sequence Generator, and then select Properties.

The Subject Sequence Generator Properties dialog box appears.

For detailed information about the Properties dialog box, see the SAS Data Integration Studio: User's Guide or the SAS Data Integration Studio online Help.
Click the Options tab.
(Optional) From the Update Source drop-down list, select a value.
- NO indicates that the source table is not modified (that is, PROC SORT output is sent to a work table).
- YES indicates that the source table is modified directly by PROC SORT in the code generation.
From the Sequence Key Variable drop-down list, select the name of the sequence variable in the target domain (–SEQ).
In the Business Keys list, adjust the order of the available keys.

This list identifies the keys in the source table. Use these keys to sort the data.
From the Subject Variable drop-down list, select a variable.

The subject variable represents the unique subject identifier (USUBJID).
Click OK.
Save and run the job.

SAS Data Integration Studio generates the SAS code for transforming, and then submits the code to SAS. The –SEQ variable is populated with a sequence number that is unique for each record for each subject.