Extracting and Comparing Unique VISIT and VISITNUM Values from SDTM Datasets

Author: [Your Name]

Date: [Creation Date]

In clinical trials, the VISIT and VISITNUM variables are key identifiers for subject visits. Ensuring that all datasets have consistent visit data and that it aligns with the planned visits recorded in the TV (Trial Visits) domain is crucial for accurate data analysis. This post presents a SAS macro that automates the extraction of unique VISIT and VISITNUM values across all SDTM datasets in a library and compares them to those found in the TV domain.

Program Overview

The SAS macro program:

• Extracts unique VISIT and VISITNUM values from all SDTM datasets in the specified library.
• Compares these values against those recorded in the TV domain.
• Highlights any discrepancies between the SDTM datasets and the TV domain.

Macro Code

Here’s the macro that performs the task:

%macro compare_visit(libname=);

    /* Step 1: Get the unique VISIT and VISITNUM values from the TV domain */
    proc sql;
        create table tv_visit as
        select distinct VISIT, VISITNUM 
        from &libname..TV
        where VISIT is not missing and VISITNUM is not missing;
    quit;

    /* Step 2: Get the list of datasets in the library containing both VISIT and VISITNUM */
    proc sql noprint;
        select memname 
        into :dslist separated by ' '
        from sashelp.vcolumn
        where libname = upcase("&libname")
          and name in ('VISIT', 'VISITNUM')
        group by memname
        having count(distinct name) = 2; /* Ensure both VISIT and VISITNUM are present */
    quit;

    /* Step 3: Check if any datasets were found */
    %if &sqlobs = 0 %then %do;
        %put No datasets in &libname contain both VISIT and VISITNUM variables.;
    %end;
    %else %do;
        %put The following datasets contain both VISIT and VISITNUM variables: &dslist;

        /* Initialize an empty dataset for combined VISIT and VISITNUM values */
        data combined_visits;
            length Dataset $32 VISIT $200 VISITNUM 8;
            stop;
        run;

        /* Step 4: Loop through each dataset */
        %let ds_count = %sysfunc(countw(&dslist));
        %do i = 1 %to &ds_count;
            %let dsname = %scan(&dslist, &i);

            /* Extract unique VISIT and VISITNUM values, excluding UNSCHEDULED visits */
            proc sql;
                create table visit_&dsname as
                select distinct "&&dsname" as Dataset, VISIT, VISITNUM
                from &libname..&&dsname
                where VISIT is not missing and VISITNUM is not missing
                  and VISIT not like 'UNSCH%'; /* Exclude UNSCHEDULED visits */
            quit;

            /* Append to the combined dataset */
            proc append base=combined_visits data=visit_&dsname force;
            run;
        %end;

        /* Step 5: Compare combined VISIT/VISITNUM with TV domain */
        proc sql;
            create table visit_comparison as
            select a.*, b.Dataset as In_SDTC_Dataset
            from tv_visit a
            left join combined_visits b
            on a.VISIT = b.VISIT and a.VISITNUM = b.VISITNUM
            order by VISITNUM, VISIT;
        quit;

        /* Step 6: Display the comparison results */
        proc print data=visit_comparison;
        title "Comparison of VISIT/VISITNUM between TV and SDTM Datasets (Excluding Unscheduled Visits)";
        run;
    %end;

%mend compare_visit;

/* Run the macro by specifying your SDTM library name */
%compare_visit(libname=sdtm);

How the Macro Works

This macro performs the following steps:

1. It first extracts all unique VISIT and VISITNUM values from the TV domain.
2. It then identifies all datasets in the specified library that contain the VISIT and VISITNUM variables by querying the metadata table SASHELP.VCOLUMN.
3. For each identified dataset, the macro extracts the distinct VISIT and VISITNUM values and appends them into a consolidated dataset.
4. Finally, it compares the combined results from the SDTM datasets against the values in the TVff domain and displays any discrepancies.

Use Case

This macro is especially useful when checking that all actual visits recorded in the SDTM datasets align with the planned visits documented in the TV domain. Ensuring consistency between these values is essential for accurate clinical trial reporting and analysis.

Example of Use:

%compare_visit(libname=sdtm);

In this example, the macro will search for VISIT and VISITNUM variables in the SDTM datasets located in the sdtm library and compare them with the values in the TV domain.

Conclusion

By automating the process of extracting and comparing VISIT and VISITNUM values, this macro simplifies what could otherwise be a tedious and error-prone task. It ensures that all visit data is consistent and complete, aligning the planned and actual visits in the SDTM datasets.

Feel free to adapt this macro to meet your specific needs in clinical trials data management!