Finding EPOCH Values in SDTM Datasets using a SAS Macro

Author: [Sarath]

Date: [05SEP2024]

The EPOCH variable is essential in many SDTM datasets as it helps describe the period during which an event, observation, or assessment occurs. In clinical trials, correctly capturing and analyzing the EPOCH variable across datasets is crucial. This post walks through a SAS macro program that automates the process of finding all EPOCH values from any dataset within an entire library of SDTM datasets.

Program Overview

This macro program loops through all the datasets in a specified library, checks for the presence of the EPOCH variable, and extracts the unique values of EPOCH from each dataset. It then consolidates the results and displays them for review.

Key Features:

• Automatically identifies SDTM datasets containing the EPOCH variable.
• Extracts unique values from the EPOCH variable for each dataset.
• Combines results into a single dataset for ease of review.

Macro Code

Here’s the macro that performs the task:

%macro find_epoch(libname=);

    /* Get a list of all datasets in the library */
    proc sql noprint;
        select memname
        into :dslist separated by ' '
        from sashelp.vcolumn
        where libname = upcase("&libname")
          and name = 'EPOCH';
    quit;

    /* Check if any dataset contains the EPOCH variable */
    %if &sqlobs = 0 %then %do;
        %put No datasets in &libname contain the variable EPOCH.;
    %end;
    %else %do;
        %put The following datasets contain the EPOCH variable: &dslist;

        /* Loop through each dataset and extract unique EPOCH values */
        %let ds_count = %sysfunc(countw(&dslist));
        %do i = 1 %to &ds_count;
            %let dsname = %scan(&dslist, &i);
            
            /* Extract unique values of EPOCH */
            proc sql;
                create table epoch_&dsname as
                select distinct '&&dsname' as Dataset, EPOCH
                from &libname..&&dsname
                where EPOCH is not missing;
            quit;
        %end;

        /* Combine the results from all datasets */
        data all_epochs;
            set epoch_:;
        run;

        /* Display the results */
        proc print data=all_epochs;
        title "Unique EPOCH values across datasets in &libname";
        run;
    %end;

%mend find_epoch;

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

How the Macro Works

The macro works by querying the SASHELP.VCOLUMN metadata table to check for the presence of the EPOCH variable in any dataset. It loops through the datasets that contain the variable, extracts distinct values, and aggregates the results into a single dataset.

Steps:

1. Identifies all datasets in the specified library.
2. Checks each dataset for the EPOCH variable.
3. For datasets containing EPOCH, it extracts unique values.
4. Combines the unique values from all datasets into one result dataset.

Use Case

Imagine you have a large collection of SDTM datasets and need to quickly check which datasets contain the EPOCH variable and what unique values it holds. Running this macro allows you to do this across your entire library with minimal effort.

Example of Use:

%find_epoch(libname=sdtm);

In this example, the macro will search for the EPOCH variable in the SDTM datasets stored in the library named SDTM. It will then display the unique values of EPOCH found in those datasets.

Conclusion

This macro simplifies the task of analyzing the EPOCH variable across multiple datasets in a library, saving time and reducing manual effort. By leveraging the power of PROC SQL and macros, you can automate this otherwise tedious process.

Feel free to adapt and expand this macro to suit your specific needs! Happy coding!