PROC COMPARE Tips and Techniques in SDTM Programming: A Comprehensive Guide with Examples

PROC COMPARE Tips and Techniques in SDTM Programming: A Comprehensive Guide with Examples

PROC COMPARE is a powerful and versatile procedure in SAS that is extensively used in SDTM (Study Data Tabulation Model) programming for validating and verifying datasets. It allows you to compare two datasets to identify differences, ensuring data consistency, integrity, and accuracy. This expanded report provides detailed tips, techniques, and advanced strategies for effectively using PROC COMPARE in SDTM programming, along with practical examples.

1. Basic Usage of PROC COMPARE

At its core, PROC COMPARE compares two datasets—referred to as the "base" and "compare" datasets—to highlight differences. This is particularly useful in SDTM programming when verifying that derived datasets match the original data or when comparing outputs from independent programming.

/* Basic example of PROC COMPARE */
proc compare base=sdtm.dm compare=qc.dm;
run;

In this example, the SDTM domain dataset dm is compared with a QC (Quality Control) dataset to identify any discrepancies. This straightforward use case highlights any differences in data content between the two datasets.

2. Key Options for PROC COMPARE

PROC COMPARE offers various options to customize the comparison process, making it more suitable for specific tasks in SDTM programming. Below are some of the most useful options with detailed explanations and examples.

2.1. ID Statement

The ID statement is crucial in SDTM datasets, where each record typically represents a specific subject or event. This statement specifies key variables that uniquely identify observations in the datasets being compared.

/* Using ID statement */
proc compare base=sdtm.dm compare=qc.dm;
    id usubjid;
run;

In this example, the unique subject identifier usubjid is specified as the key variable, ensuring that records are compared correctly between the base and compare datasets. This helps in cases where the datasets may not be sorted identically but need to be compared observation by observation based on the unique identifier.

2.2. VAR Statement

The VAR statement allows you to specify which variables to compare. This is particularly useful when you only need to validate specific variables within a dataset, reducing unnecessary output and focusing the comparison on critical variables.

/* Using VAR statement */
proc compare base=sdtm.lb compare=qc.lb;
    id usubjid visitnum lbtestcd;
    var lbtest lbcat lbreslb;
run;

In this example, only the variables lbtest, lbcat, and lbreslb are compared between the two datasets. This targeted approach helps ensure that only the variables of interest are scrutinized, which is particularly useful in large datasets where comparing every variable might be unnecessary or overwhelming.

2.3. CRITERION Option

The CRITERION option specifies the tolerance level for numerical comparisons. This is particularly useful when dealing with floating-point numbers, where minor differences might occur due to rounding. Setting an appropriate criterion ensures that small, insignificant differences do not trigger false discrepancies.

/* Using CRITERION option for tolerance */
proc compare base=sdtm.vs compare=qc.vs criterion=0.01;
    id usubjid visitnum vstestcd;
run;

Here, a criterion of 0.01 is set, meaning that differences smaller than 0.01 between the base and compare datasets are ignored. This is particularly important in clinical data where small numerical differences may arise from different levels of precision or rounding methods in different software or programming environments.

2.4. LISTALL Option

The LISTALL option generates a detailed report of all differences, including variable names, values, and observation numbers. This comprehensive output is useful for a thorough review of discrepancies, especially when you need to document or understand all the differences between datasets.

/* Using LISTALL option */
proc compare base=sdtm.ae compare=qc.ae listall;
    id usubjid aeseq;
run;

This example generates a detailed list of all differences found in the ae dataset, helping you pinpoint specific issues. The detailed nature of this output is particularly useful in audits or when you need to present a comprehensive comparison report to stakeholders.

2.5. OUT= and OUTBASE/OUTCOMP/OUTDIFF Options

These options allow you to output the results of the comparison to a dataset. The OUT= option specifies the output dataset, while OUTBASE, OUTCOMP, and OUTDIFF control which observations are included in the output dataset.

/* Outputting comparison results to a dataset */
proc compare base=sdtm.ex compare=qc.ex out=comp_results outbase outcomp outdiff noprint;
    id usubjid exseq;
run;

This example outputs the comparison results to a dataset named comp_results, including all base and compare observations, as well as those that differ. The noprint option suppresses the printed output in the log, focusing the results on the output dataset, which can then be reviewed or used in further processing.

2.6. ALLOBS Option

The ALLOBS option forces PROC COMPARE to compare all observations, even if the datasets are of different lengths. This can be useful when you want to ensure that every record in one dataset is accounted for in the other.

/* Using ALLOBS option */
proc compare base=sdtm.vs compare=qc.vs allobs;
    id usubjid visitnum vstestcd;
run;

This option is particularly useful when you suspect that there might be missing or extra records in one of the datasets, ensuring that the comparison is exhaustive.

2.7. NOVALUES Option

If you’re only interested in whether differences exist without needing to see the actual differing values, the NOVALUES option can be used to suppress the detailed listing of differences.

/* Using NOVALUES option */
proc compare base=sdtm.dm compare=qc.dm novalues;
    id usubjid;
run;

This example will report if differences are found but will not list the specific values that differ. This is useful for a quick check when detailed information about differences is not necessary.

3. Handling Common Issues in PROC COMPARE

While PROC COMPARE is powerful, certain issues can arise during comparisons. Below are some common problems and strategies to address them effectively.

3.1. Dealing with Missing Values

Missing values can complicate comparisons, as PROC COMPARE may treat them differently depending on the context. The COMPARE and WITH statements can help manage these situations by allowing you to explicitly compare specific variables, even when some may be missing.

/* Handling missing values */
proc compare base=sdtm.cm compare=qc.cm;
    id usubjid cmseq;
    with cmdose cmtrt;
run;

This example compares the variables cmdose and cmtrt between the base and compare datasets, even if some of these variables might have missing values. This targeted approach ensures that missing data does not result in an incomplete comparison.

3.2. Comparing Datasets with Different Structures

If the datasets have different structures (e.g., different variable names or lengths), use the NOMISSING or NOSOURCE options to focus the comparison on the variables that are common to both datasets. This ensures that the comparison is relevant and does not get bogged down by structural differences that are not meaningful to the comparison task.

/* Comparing datasets with different structures */
proc compare base=sdtm.suppae compare=qc.suppae nosource;
    id usubjid qnam;
run;

The NOSOURCE option excludes variables that are only present in one of the datasets, allowing for a focused comparison. This is particularly useful when datasets have evolved over time or when you’re comparing datasets from different sources or stages of data processing.

3.3. Comparing Character Variables with Different Lengths

When comparing character variables, differences in variable lengths can cause issues. Use the MAXPRINT= option to limit the number of differing values printed in the output, especially when dealing with large text fields.

/* Comparing character variables with different lengths */
proc compare base=sdtm.cm compare=qc.cm maxprint=50;
    id usubjid cmseq;
    var cmtrt;
run;

This example limits the output to the first 50 differences found, which can be helpful when comparing datasets with many observations and potential differences in character variables.

4. Advanced Techniques for PROC COMPARE

For more complex comparisons or when working with large datasets, advanced techniques can enhance the functionality of PROC COMPARE. Below are several strategies to take your comparisons to the next level.

4.1. Using Formats for Customized Comparisons

Applying formats to variables before comparison can help standardize the data and make the comparison more meaningful, especially for categorical variables. This approach is particularly useful in SDTM programming, where data may come from different sources or be transformed in different ways.

/* Using formats for comparison */
proc format;
    value sexfmt
        1 = 'Male'
        2 = 'Female';
run;

proc compare base=sdtm.dm compare=qc.dm;
    id usubjid;
    var sex;
    format sex sexfmt.;
run;

This example applies a custom format to the sex variable, ensuring that the comparison is done on standardized values. This technique is useful when datasets may use different coding schemes or when you want to compare datasets in a more human-readable format.

4.2. Limiting the Comparison to Key Observations

If you're only interested in comparing key observations (e.g., critical time points or specific subjects), you can subset the data before running PROC COMPARE. This focused approach ensures that the comparison is relevant and not cluttered by extraneous data.

/* Subsetting data before comparison */
data sdtm_sub;
    set sdtm.vs;
    where visitnum in (1, 4, 8); /* Only key visits */
run;

proc compare base=sdtm_sub compare=qc.vs;
    id usubjid visitnum vstestcd;
run;

This example subsets the vs dataset to only include key visits before performing the comparison, focusing on the most important data points. This technique is particularly useful when you are only interested in certain aspects of the data, such as baseline or endpoint visits.

4.3. Automating Comparisons with Macros

You can automate the comparison process across multiple datasets using SAS macros, making it easier to perform routine checks across an entire SDTM package. This approach is especially useful in large clinical trials where many datasets need to be compared on a regular basis.

%macro compare_datasets(base=, compare=, idvars=);
    proc compare base=&base compare=&compare;
        id &idvars;
    run;
%mend compare_datasets;

%compare_datasets(base=sdtm.dm, compare=qc.dm, idvars=usubjid);
%compare_datasets(base=sdtm.lb, compare=qc.lb, idvars=usubjid lbtestcd visitnum);
%compare_datasets(base=sdtm.ae, compare=qc.ae, idvars=usubjid aeseq);

This macro simplifies the process of comparing multiple SDTM datasets, ensuring consistency across your validation efforts. By automating these comparisons, you can save time and reduce the risk of human error in routine QC processes.

4.4. Using BY-Group Comparisons

BY-group processing in PROC COMPARE allows you to compare subsets of your data independently. This is particularly useful in SDTM datasets where comparisons need to be made within specific groups, such as treatment arms or study phases.

/* Using BY-group comparisons */
proc compare base=sdtm.lb compare=qc.lb;
    by usubjid visitnum;
    id lbtestcd;
run;

In this example, the comparison is done within each combination of usubjid and visitnum, allowing for a more granular comparison that considers the structure of the data. This is especially useful when the same variables may have different expected values in different groups, such as treatment vs. control groups.

4.5. Comparing Large Datasets Efficiently

When working with large datasets, PROC COMPARE can become resource-intensive. Using the MAXPRINT= option to limit output, and the OUTNOEQUAL option to exclude matching records from the output, can help manage performance.

/* Efficiently comparing large datasets */
proc compare base=sdtm.lb compare=qc.lb maxprint=(50,10) outnoequal;
    id usubjid visitnum lbtestcd;
run;

In this example, the output is limited to the first 50 differences and 10 variable differences per observation. The OUTNOEQUAL option ensures that only records with differences are included in the output, reducing the amount of data that needs to be processed and reviewed.

5. Interpreting PROC COMPARE Output

Understanding the output from PROC COMPARE is crucial for identifying and addressing discrepancies. The key sections of the output include:

• Summary Section: Provides a quick overview of the comparison, including the number of matching and differing observations and variables.
• Observation Comparison: Details specific differences at the observation level, including values from both the base and compare datasets.
• Variable Comparison: Lists variables that differ between the datasets, including discrepancies in attributes like type and length.

/* Example output interpretation */
proc compare base=sdtm.dm compare=qc.dm;
    id usubjid;
run;

/* Sample output:
Comparison Results for Data Set Comparison

Summary of Results:
-------------------
Variables compared: 10
Observations compared: 100
Observations with discrepancies: 2

Observation Comparison:
------------------------
usubjid: 101
Base: Age=35, Compare: Age=36

usubjid: 102
Base: Sex=Male, Compare: Sex=Female
*/

The summary section gives you a quick snapshot of the comparison, helping you quickly assess whether the datasets are largely in agreement or if significant differences exist. The detailed observation comparison highlights exactly where the datasets diverge, making it easier to pinpoint and correct errors.

5.1. Using Output Datasets for Further Analysis

Sometimes, the comparison output needs to be analyzed further or shared with others. By directing the output to a dataset using the OUT= option, you can perform additional analyses, create custom reports, or easily integrate the results into other processes.

/* Creating an output dataset for further analysis */
proc compare base=sdtm.vs compare=qc.vs out=comp_results outbase outcomp outdiff;
    id usubjid visitnum vstestcd;
run;

/* Further analysis on the comparison results */
proc freq data=comp_results;
    tables _type_;
run;

In this example, the comparison results are saved to a dataset, and a frequency analysis is performed on the comparison type. This can help identify common issues, such as frequent mismatches in certain variables or patterns in the differences.

5.2. Generating Reports from Comparison Results

For documentation or auditing purposes, you may need to generate reports from the comparison results. By using ODS (Output Delivery System) along with PROC COMPARE, you can create professional-looking reports in various formats.

/* Generating a PDF report of comparison results */
ods pdf file="comparison_report.pdf";
proc compare base=sdtm.ae compare=qc.ae listall;
    id usubjid aeseq;
run;
ods pdf close;

This example generates a PDF report that documents all differences found in the comparison, making it easy to share and review with team members or auditors.

6. Practical Applications of PROC COMPARE in SDTM Programming

In SDTM programming, PROC COMPARE can be used for a variety of practical applications beyond simple dataset comparison. Here are some advanced use cases:

6.1. Validating Derived Variables

Use PROC COMPARE to ensure that derived variables in SDTM datasets are calculated correctly by comparing them to independently calculated values.

/* Validating derived variables */
proc compare base=sdtm.vs compare=derived.vs;
    id usubjid visitnum vstestcd;
    var vsorres vsstresn;
run;

In this example, the original SDTM dataset is compared with a derived dataset to validate the derivation of variables such as vsstresn (numeric standard result).

6.2. Cross-Checking SDTM Domains

Ensure consistency across related SDTM domains by comparing them with relevant metadata or reference datasets.

/* Cross-checking SDTM domains */
proc compare base=sdtm.ex compare=sdtm.dm;
    id usubjid;
    var exstdtc exendtc;
run;

This comparison checks that the exposure start and end dates in the EX domain align with the demographics data in the DM domain.

6.3. QC of SDTM Mapping

During SDTM mapping, compare the mapped datasets with the original source datasets to ensure that all required transformations were applied correctly.

/* QC of SDTM mapping */
proc compare base=raw_data compare=sdtm.mapped_data;
    id usubjid visitnum;
run;

This example compares the raw source data with the mapped SDTM dataset, ensuring that the mapping process has been performed correctly.

Conclusion

PROC COMPARE is an indispensable tool in SDTM programming, providing robust capabilities for data validation, quality control, and dataset comparison. By mastering the tips, techniques, and advanced strategies outlined in this report, you can ensure that your SDTM datasets are accurate, consistent, and ready for submission or further analysis. Whether you're dealing with simple comparisons or complex dataset validations, PROC COMPARE offers the flexibility and power needed to meet the stringent requirements of clinical data management.

Common Sense Tips and Clever Tricks for SAS Programming

Common Sense Tips and Clever Tricks for SAS Programming

SAS is a powerful tool for data analysis, but to make the most of it, you need to apply not just technical skills but also practical, common-sense strategies. This report highlights useful tips and clever tricks that can enhance your SAS programming efficiency, reduce errors, and make your code more robust and maintainable, complete with practical examples.

1. Keep Your Code Simple and Readable

One of the most important principles in SAS programming is to keep your code simple and readable. This makes it easier to debug, maintain, and understand, especially when sharing your code with others.

1.1. Use Meaningful Variable Names

Choose variable names that clearly describe the data they hold. This makes your code more intuitive and easier to follow.

/* Bad practice: using vague variable names */
data work1;
    set olddata;
    x1 = x2 + x3;
run;

/* Good practice: using meaningful variable names */
data employee_salaries;
    set company_data;
    total_salary = base_salary + bonus;
run;

1.2. Comment Your Code

Comments are crucial for explaining what your code does, why certain decisions were made, and how it works. This is especially helpful when you revisit your code after some time or when others need to understand your logic.

/* Calculating total compensation for each employee */
data employee_salaries;
    set company_data;
    total_salary = base_salary + bonus; /* Base salary plus bonus */
run;

1.3. Use Indentation and Formatting

Proper indentation and formatting improve the readability of your code, making it easier to spot logical blocks and understand the structure of your program.

/* Good formatting example */
data filtered_data;
    set raw_data;
    if age > 18 then do;
        adult_flag = 1;
    end;
    else do;
        adult_flag = 0;
    end;
run;

2. Optimize Your Data Steps

Data steps are at the heart of SAS programming. Optimizing them can significantly improve performance, especially when working with large datasets.

2.1. Avoid Unnecessary Sorting

Sorting large datasets can be resource-intensive. Only sort data when absolutely necessary, and if possible, use indexed variables to reduce the need for sorting.

/* Bad practice: unnecessary sorting */
proc sort data=large_dataset;
    by customer_id;
run;

/* Good practice: using indexed variables or avoiding sorting */
proc sql;
    create index customer_id on large_dataset(customer_id);
quit;

/* Or avoid sorting altogether if not needed */
data sorted_data;
    set large_dataset;
    by customer_id;
run;

2.2. Use WHERE Instead of IF for Subsetting Data

When subsetting data, use the WHERE statement instead of IF within data steps. The WHERE statement processes the data more efficiently because it applies the condition before reading the data.

/* Inefficient: using IF statement */
data adults;
    set large_dataset;
    if age >= 18;
run;

/* Efficient: using WHERE statement */
data adults;
    set large_dataset;
    where age >= 18;
run;

2.3. Use KEEP and DROP to Manage Variables

To minimize the size of your datasets and improve processing speed, only keep the variables you need using the KEEP or DROP statements.

/* Keeping only necessary variables */
data cleaned_data;
    set raw_data(keep=customer_id age total_purchase);
run;

/* Dropping unnecessary variables */
data cleaned_data;
    set raw_data(drop=middle_name social_security_number);
run;

3. Efficiently Manage and Merge Datasets

Combining datasets is a common task in SAS, but it can be done more or less efficiently depending on how you approach it.

3.1. Use MERGE Carefully

When merging datasets, make sure both datasets are sorted by the key variables and that you understand the implications of using different types of merges (e.g., one-to-one, one-to-many).

/* Correctly merging datasets */
proc sort data=dataset1;
    by customer_id;
run;

proc sort data=dataset2;
    by customer_id;
run;

data merged_data;
    merge dataset1(in=a) dataset2(in=b);
    by customer_id;
    if a and b; /* Keeps only matching records */
run;

3.2. Use HASH Objects for Lookups

HASH objects provide a faster alternative to merging or joining datasets when you need to perform lookups. They are particularly useful when the lookup dataset is small but the main dataset is large.

/* Using HASH object for lookups */
data main_data;
    if _n_ = 1 then do;
        declare hash lookup(dataset:'lookup_table');
        lookup.defineKey('customer_id');
        lookup.defineData('customer_name', 'customer_status');
        lookup.defineDone();
    end;

    set large_main_data;
    if lookup.find() = 0 then output;
run;

4. Error Handling and Debugging

Being able to handle errors and debug effectively is critical in SAS programming. This section provides tips for identifying and resolving issues in your code.

4.1. Use the SAS Log for Debugging

The SAS log is your best friend when it comes to debugging. Regularly check the log for errors, warnings, and notes that can help identify where things are going wrong.

/* Example of checking the log */
data new_data;
    set old_data;
    /* Expecting a numeric value, check for issues */
    if missing(numeric_var) then put "WARNING: Missing value for " numeric_var=;
run;

4.2. Utilize the PUTLOG Statement

Use PUTLOG to print custom messages to the log, which can be invaluable for understanding how your code is executing and where it might be failing.

/* Example using PUTLOG for debugging */
data test_data;
    set raw_data;
    if age > 65 then do;
        putlog "NOTE: Senior citizen detected: " customer_id= age=;
    end;
run;

4.3. Implement Error Handling with %IF-%THEN Statements

In macros, use %IF-%THEN statements to implement error handling, allowing your code to gracefully handle unexpected situations.

%macro check_input(value);
    %if &value. =  %then %do;
        %put ERROR: Input value is missing;
        %return;
    %end;
    %else %do;
        %put NOTE: Input value is &value;
    %end;
%mend check_input;

%check_input(42);
%check_input();

5. Efficient Data Manipulation and Transformation

Data manipulation is a core task in SAS programming. Using efficient techniques can save time and reduce the complexity of your code.

5.1. Use Arrays for Repetitive Tasks

When performing the same operation on multiple variables, arrays can simplify your code and reduce the risk of errors.

/* Using arrays to standardize scores */
data standardized;
    set scores;
    array score_vars {*} score1-score5;
    array z_scores {*} z_score1-z_score5;

    do i = 1 to dim(score_vars);
        z_scores{i} = (score_vars{i} - mean(score_vars{i})) / std(score_vars{i});
    end;

    drop i;
run;

5.2. Leverage PROC SQL for Data Manipulation

PROC SQL is powerful for complex data manipulation, such as subsetting, joining, and summarizing data. It can often achieve in one step what would require multiple data steps and procedures.

/* Using PROC SQL for data manipulation */
proc sql;
    create table high_spenders as
    select customer_id, sum(purchase_amount) as total_spent
    from transactions
    group by customer_id
    having total_spent > 1000;
quit;

6. Macros for Reusability and Efficiency

Macros in SAS allow you to write reusable code that can be applied across multiple datasets or projects, saving time and ensuring consistency.

6.1. Create Reusable Macros

Create macros for tasks you perform frequently, such as data validation, reporting, or standard calculations.

%macro calculate_bmi(weight, height);
    %let bmi = %sysevalf(&weight / (&height / 100)**2);
    %put BMI is &bmi;
%mend calculate_bmi;

%calculate_bmi(75, 180);

6.2. Use Macro Parameters to Customize Behavior

Macro parameters allow you to customize the behavior of your macros, making them more flexible and powerful.

%macro summarize_data(dataset, var);
    proc means data=&dataset n mean std min max;
        var &var;
    run;
%mend summarize_data;

%summarize_data(sales_data, revenue);
%summarize_data(employee_data, salary);

7. Best Practices for Code Organization and Documentation

Good code organization and documentation practices are essential for maintaining and sharing your SAS programs.

7.1. Organize Your Code into Sections

Break your code into logical sections with clear headings, making it easier to navigate and maintain.

/* Section 1: Data Import */
proc import datafile="data.xlsx" out=raw_data dbms=xlsx replace;
    sheet="Sheet1";
    getnames=yes;
run;

/* Section 2: Data Cleaning */
data cleaned_data;
    set raw_data;
    /* Cleaning logic here */
run;

/* Section 3: Data Analysis */
proc means data=cleaned_data;
    var sales;
run;

7.2. Create and Maintain a Code Library

Keep a library of commonly used code snippets, macros, and templates that you can reuse across projects. This helps ensure consistency and saves time.

/* Example: A simple code library structure */
- /CodeLibrary
    - data_cleaning_macros.sas
    - data_analysis_templates.sas
    - custom_formats.sas

Conclusion

Applying these common sense tips and clever tricks in your SAS programming can greatly enhance your efficiency, reduce errors, and make your code more maintainable. By focusing on simplicity, readability, and reusability, you can ensure that your SAS programs are not only effective but also easy to understand and share with others.

Effective data management is crucial for the success of any data-driven project

Effective data management is crucial for the success of any data-driven project, especially in clinical trials and research. SAS provides a powerful toolkit for managing, cleaning, transforming, and analyzing data. This report presents essential SAS programs that can significantly improve your data management processes.

1. Data Import and Export

Managing data often starts with importing datasets from various sources and exporting them for analysis or sharing. SAS offers several procedures to handle different data formats.

1.1. Importing Data from Excel

To import data from an Excel file, use the PROC IMPORT procedure. This allows you to easily bring data into SAS from Excel spreadsheets.

/* Importing data from an Excel file */
proc import datafile="/path/to/your/file.xlsx"
    out=mydata
    dbms=xlsx
    replace;
    sheet="Sheet1";
    getnames=yes;
run;

1.2. Exporting Data to Excel

You can also export SAS datasets to Excel using the PROC EXPORT procedure.

/* Exporting data to an Excel file */
proc export data=mydata
    outfile="/path/to/your/output.xlsx"
    dbms=xlsx
    replace;
    sheet="ExportedData";
run;

2. Data Cleaning and Validation

Data cleaning is an essential part of data management. It involves identifying and correcting errors or inconsistencies in the data to ensure accuracy and reliability.

2.1. Checking for Missing Values

Use the following SAS program to identify missing values across all variables in your dataset.

/* Checking for missing values */
proc means data=mydata nmiss;
    var _numeric_;
run;

proc freq data=mydata;
    tables _character_ / missing;
run;

2.2. Removing Duplicates

Duplicate records can lead to skewed analysis results. The following SAS program removes duplicate records based on key variables.

/* Removing duplicates */
proc sort data=mydata nodupkey;
    by subjectid visitnum;
run;

2.3. Data Validation

Data validation ensures that your dataset meets specific criteria, such as valid ranges for variables. Use the following SAS program to validate age and weight in a clinical dataset.

/* Validating age and weight */
data validated;
    set mydata;
    if age < 0 or age > 100 then put "ERROR: Invalid age for subject " subjectid= age=;
    if weight <= 0 then put "ERROR: Invalid weight for subject " subjectid= weight=;
run;

3. Data Transformation

Data transformation involves modifying the structure, values, or format of data to prepare it for analysis. This can include creating new variables, aggregating data, or reshaping datasets.

3.1. Creating New Variables

Use the following program to create a new variable, such as Body Mass Index (BMI), from existing variables.

/* Calculating BMI */
data mydata;
    set mydata;
    bmi = weight / (height/100)**2;
    format bmi 8.2;
run;

3.2. Aggregating Data

Aggregate data to summarize it by key variables, such as calculating the average age of patients by treatment group.

/* Aggregating data */
proc means data=mydata n mean;
    class treatment;
    var age;
    output out=summary_data mean=mean_age;
run;

3.3. Reshaping Data

Reshape datasets from wide to long format or vice versa. Use PROC TRANSPOSE to pivot datasets.

/* Reshaping data from wide to long format */
proc transpose data=mydata out=longdata;
    by subjectid;
    var visit1 visit2 visit3;
    id visit;
run;

4. Data Merging and Joining

Merging and joining datasets are common tasks in data management, allowing you to combine information from multiple sources.

4.1. One-to-One Merge

A one-to-one merge combines two datasets based on common key variables.

/* One-to-one merge */
data merged_data;
    merge dataset1 dataset2;
    by subjectid;
run;

4.2. One-to-Many Merge

A one-to-many merge combines datasets where one key variable corresponds to multiple records in the other dataset.

/* One-to-many merge */
data merged_data;
    merge master_data(in=a) detail_data(in=b);
    by subjectid;
    if a and b;
run;

4.3. Left Join

Perform a left join to keep all records from the first dataset and only matching records from the second dataset.

/* Left join */
proc sql;
    create table left_join as
    select a.*, b.*
    from dataset1 as a
    left join dataset2 as b
    on a.subjectid = b.subjectid;
quit;

5. Data Output and Reporting

After managing and analyzing data, the next step is to output the results in a format suitable for reporting or further analysis.

5.1. Generating Summary Reports

Use PROC REPORT to create detailed summary reports that can be customized to meet specific reporting requirements.

/* Generating a summary report */
proc report data=summary_data nowd;
    column treatment mean_age;
    define treatment / group 'Treatment Group';
    define mean_age / mean 'Average Age';
run;

5.2. Exporting Data to CSV

Export datasets to CSV files for easy sharing or further analysis in other software.

/* Exporting data to CSV */
proc export data=summary_data
    outfile="/path/to/output.csv"
    dbms=csv
    replace;
run;

5.3. Creating PDF Reports

Create professional-looking PDF reports directly from SAS using ODS (Output Delivery System).

/* Creating a PDF report */
ods pdf file="/path/to/report.pdf";
proc report data=summary_data nowd;
    column treatment mean_age;
    define treatment / group 'Treatment Group';
    define mean_age / mean 'Average Age';
run;
ods pdf close;

6. Automating Data Management Tasks

Automating repetitive data management tasks can save time and reduce the risk of human error. Use SAS macros to create reusable code blocks that can be applied across different datasets or projects.

6.1. Macro for Data Cleaning

Create a macro that standardizes variable names, removes duplicates, and checks for missing values across different datasets.

%macro clean_data(dataset);
    /* Standardize variable names */
    proc datasets lib=work nolist;
        modify &dataset;
        rename subj_id=subjectid visit_no=visitnum;
    quit;

    /* Remove duplicates */
    proc sort data=&dataset nodupkey;
        by subjectid visitnum;
    run;

    /* Check for missing values */
    proc means data=&dataset nmiss;
        var _numeric_;
    run;

    proc freq data=&dataset;
        tables _character_ / missing;
    run;
%mend clean_data;

/* Apply the macro to different datasets */
%clean_data(dataset=dataset1);
%clean_data(dataset=dataset2);

6.2. Macro for Data Export

Create a macro to automate the export of datasets to various formats, such as CSV or Excel.

%macro export_data(dataset, format=csv, outpath=);
    %if &format=csv %then %do;
        proc export data=&dataset
            outfile="&outpath./&dataset..csv"
            dbms=csv
            replace;
        run;
    %end;
    %else %if &format=xlsx %then %do;
        proc export data=&dataset
            outfile="&outpath./&dataset..xlsx"
            dbms=xlsx
            replace;
        run;
    %end;
%mend export_data;

/* Export datasets */
%export_data(dataset=summary_data, format=csv, outpath=/path/to/output);
%export_data(dataset=summary_data, format=xlsx, outpath=/path/to/output);

Conclusion

SAS provides a robust set of tools for effective data management. From data import and export to cleaning, transformation, merging, and reporting, these SAS programs can significantly enhance your ability to manage data efficiently. By incorporating macros and automation, you can streamline repetitive tasks, ensuring consistent and accurate results across projects.

Macros are powerful tools in SAS programming, especially in SDTM

Macros are powerful tools in SAS programming, especially in SDTM (Study Data Tabulation Model) programming, where they can automate repetitive tasks and ensure consistency across datasets. However, debugging macros can be challenging due to their complexity and the way they handle data. This guide provides detailed strategies and examples for effectively debugging macros in SDTM programming.

1. Use the MPRINT Option to Trace Macro Execution

The MPRINT option in SAS helps trace the execution of macro code by printing the SAS statements generated by the macro to the log. This is especially useful when you want to see the resolved code that the macro generates.

Example: Consider a macro that generates an SDTM domain. By enabling MPRINT, you can see exactly what code is being executed, helping you identify where errors might occur.

options mprint;

%macro create_dm;
   data dm;
      set rawdata;
      usubjid = subject_id;
      age = input(age_raw, 8.);
      sex = gender;
      /* More variable mappings */
   run;
%mend create_dm;

%create_dm;

With MPRINT enabled, the log will show the actual data step code generated by the macro, making it easier to identify any issues.

2. Use the MLOGIC Option to Debug Macro Logic

The MLOGIC option prints information about the macro’s logic flow, including when macros are called, when macro variables are resolved, and the values they resolve to. This helps you understand the macro’s decision-making process.

Example: Use MLOGIC to trace how a macro variable is being resolved within a loop or conditional statement.

options mlogic;

%macro check_age;
   %let min_age = 18;
   %let max_age = 65;

   %if &min_age > &max_age %then %do;
      %put ERROR: Minimum age cannot be greater than maximum age.;
   %end;
   %else %do;
      data age_check;
         set rawdata;
         if age >= &min_age and age <= &max_age then valid_age = 1;
         else valid_age = 0;
      run;
   %end;
%mend check_age;

%check_age;

With MLOGIC enabled, the log will show how the macro variables min_age and max_age are being resolved and the flow of logic within the macro.

3. Use the SYMBOLGEN Option to Track Macro Variable Resolution

The SYMBOLGEN option prints the resolution of macro variables to the log. This is particularly useful for debugging issues related to macro variable values, especially when those variables are used in data steps or PROC SQL.

Example: If a macro is not producing the expected results, use SYMBOLGEN to check how each macro variable is being resolved.

options symbolgen;

%macro filter_by_sex(sex=);
   data filtered;
      set dm;
      where sex = "&sex.";
   run;
%mend filter_by_sex;

%filter_by_sex(sex=M);

The log will show how the sex variable is being resolved, helping you confirm that the correct value is being passed to the where statement.

4. Incorporate PUTLOG Statements for Custom Debugging

While MPRINT, MLOGIC, and SYMBOLGEN provide automatic logging, adding PUTLOG statements within your macros allows for custom debugging messages. This can be particularly helpful when you need to check specific conditions or values during macro execution.

Example: Use PUTLOG to debug a conditional macro that applies different transformations based on input parameters.

%macro transform_data(var=, method=);
   %if &method = log %then %do;
      data transformed;
         set rawdata;
         &var._log = log(&var.);
         putlog "NOTE: Log transformation applied to " &var=;
      run;
   %end;
   %else %if &method = sqrt %then %do;
      data transformed;
         set rawdata;
         &var._sqrt = sqrt(&var.);
         putlog "NOTE: Square root transformation applied to " &var=;
      run;
   %end;
   %else %do;
      %put ERROR: Invalid method specified. Use "log" or "sqrt".;
   %end;
%mend transform_data;

%transform_data(var=height, method=log);

The PUTLOG statement will output a note to the log indicating which transformation was applied, or an error message if an invalid method was specified.

5. Test Macros with Simple, Controlled Inputs

Before using a macro in a complex scenario, test it with simple, controlled inputs to ensure it behaves as expected. This helps isolate the macro's functionality and identify potential issues in a controlled environment.

Example: Test a macro that standardizes date formats with a small sample dataset to ensure it handles various date formats correctly.

data test_dates;
   input rawdate $10.;
   datalines;
2021-01-01
01JAN2021
2021/01/01
;
run;

%macro standardize_date(datevar=);
   data standardized;
      set test_dates;
      format &datevar yymmdd10.;
      &datevar = input(&datevar, anydtdte10.);
   run;

   proc print data=standardized;
   run;
%mend standardize_date;

%standardize_date(datevar=rawdate);

This example demonstrates testing a date standardization macro with a simple dataset to ensure it correctly processes different date formats.

6. Break Down Complex Macros into Smaller Components

Complex macros can be challenging to debug due to the multiple steps and logic involved. Breaking down a complex macro into smaller, more manageable components makes it easier to identify and fix issues.

Example: Instead of writing a single macro to process an entire SDTM domain, split it into smaller macros that handle specific tasks, such as variable mapping, data transformation, and output formatting.

%macro map_variables;
   data mapped;
      set rawdata;
      usubjid = subject_id;
      age = input(age_raw, 8.);
      sex = gender;
   run;
%mend map_variables;

%macro transform_data;
   data transformed;
      set mapped;
      if age < 18 then age_group = 'Child';
      else age_group = 'Adult';
   run;
%mend transform_data;

%macro output_data;
   proc print data=transformed;
   run;
%mend output_data;

%map_variables;
%transform_data;
%output_data;

This approach makes it easier to debug each step individually and ensures that each component works correctly before combining them into a larger process.

7. Use Macro Quoting Functions to Handle Special Characters

Special characters in macro variables can cause unexpected behavior. Macro quoting functions like %STR, %NRSTR, %QUOTE, and %NRQUOTE help handle these characters correctly.

Example: If a macro variable contains special characters like ampersands or percent signs, use macro quoting functions to prevent errors.

%let special_char_var = %str(50% discount);
%put &special_char_var;

%macro handle_special_chars(text=);
   %put NOTE: The text is: %quote(&text);
%mend handle_special_chars;

%handle_special_chars(text=Special &char handling);

The macro quoting functions ensure that special characters are handled correctly, preventing syntax errors or unexpected behavior.

8. Utilize the Debugger Macro

SAS provides a %DEBUGGER macro for debugging other macros. It allows you to step through a macro's execution and inspect variable values at each step.

Example: Use the %DEBUGGER macro to interactively debug a complex macro, inspecting variable values and execution flow in real-time.

%macro my_macro(var=);
   %local step1 step2;
   %let step1 = %eval(&var + 1);
   %let step2 = %eval(&step1 * 2);
   %put NOTE: Final value is &step2;
%mend my_macro;

/* Start the debugger */
%debugger my_macro(var=5);

The %DEBUGGER macro allows you to step through the execution of my_macro, inspect the values of step1 and step2, and identify any issues in the logic.

9. Generate Test Outputs for Verification

Generating intermediate outputs or log messages at key steps in your macro can help verify that each part of the macro is working correctly.

Example: Add steps to your macro that output temporary datasets or log specific values during execution, allowing you to verify that the macro is functioning as expected.

%macro process_data(var=);
   data step1;
      set rawdata;
      &var._step1 = &var. * 2;
   run;
   proc print data=step1;
   run;

   data step2;
      set step1;
      &var._step2 = &var._step1 + 10;
   run;
   proc print data=step2;
   run;
%mend process_data;

%process_data(var=age);

In this example, intermediate datasets step1 and step2 are printed, allowing you to verify the transformations applied to the age variable at each stage.

10. Maintain a Debugging Log for Complex Macros

For complex macros, maintain a debugging log where you document the issues encountered, the steps taken to resolve them, and any notes on how the macro behaves under different conditions. This log can be invaluable for future debugging efforts.

Example: Create a debugging log as you develop and test a macro, noting any issues with specific data inputs, unexpected behaviors, or areas of the code that required special handling.

/* Debugging Log for %process_data Macro
   - Issue: The macro fails when var contains missing values
   - Resolution: Added a check for missing values before processing
   - Note: The macro works correctly with both positive and negative values
   - Date: YYYY-MM-DD
   - Author: Your Name
*/
%macro process_data(var=);
   %if &var = . %then %do;
      %put ERROR: Missing value for &var.. Macro will not execute.;
      %return;
   %end;

   data processed;
      set rawdata;
      &var._processed = &var. * 2;
   run;
%mend process_data;

%process_data(var=age);

This debugging log helps keep track of the macro's development and any issues resolved along the way, providing a valuable resource for future maintenance or enhancements.

Conclusion

Macro debugging in SDTM programming can be challenging, but by using these techniques—such as enabling logging options, breaking down complex macros, using custom PUTLOG statements, and maintaining a debugging log—you can effectively troubleshoot and resolve issues in your macros. These practices not only help ensure that your macros run correctly but also enhance the overall quality and reliability of your SDTM programming.

Efficient Quality Control (QC) of SAS Programs: A Detailed Guide with Examples

Quality Control (QC) is a crucial process in SAS programming, ensuring that your code produces accurate and reliable results. Efficient QC practices help identify errors early, reduce rework, and ensure the final output is of high quality. This guide provides detailed strategies, examples, and best practices for effectively QCing SAS programs.

1. Understand the Objective and Requirements

Before you begin QC, it’s essential to fully understand the objective of the SAS program and the requirements it must meet. This includes understanding the input data, expected output, and any specific calculations or transformations that need to be performed.

Example: If you are QCing a program that generates summary statistics for a clinical trial, ensure you understand the statistical methods being used (e.g., mean, median, standard deviation) and the specific variables being analyzed. Knowing the study protocol and analysis plan is key to understanding what the program is supposed to do.

2. Use Independent Programming for QC

One of the most effective ways to QC a SAS program is by independently reproducing the results using a separate program. This approach helps identify errors that might not be caught by reviewing the original code alone.

Example: If the original program uses PROC MEANS to calculate summary statistics, create an independent QC program that uses PROC SUMMARY or PROC UNIVARIATE to generate the same statistics. Compare the results to ensure they match.

/* Original Program */
proc means data=studydata n mean std min max;
   var age height weight;
run;

/* QC Program */
proc summary data=studydata n mean std min max;
   var age height weight;
   output out=qc_summary;
run;

proc compare base=qc_summary compare=studydata;
run;

In this example, the PROC COMPARE step is used to check if the results from the original program match those produced by the QC program. Any discrepancies will be highlighted, allowing you to investigate further.

3. Review the SAS Log for Errors, Warnings, and Notes

The SAS log is an invaluable tool for QC. Always review the log for errors, warnings, and notes that could indicate potential issues with the code. Pay special attention to uninitialized variables, missing data, and potential data truncation.

Example: If the log contains a note about a missing variable, investigate whether the variable was expected in the dataset and why it is missing. Correct the issue in the code and rerun the program to confirm the fix.

/* Example: Checking the log for missing values */
data newdata;
   set olddata;
   if missing(var1) then put "WARNING: var1 is missing for " _N_=;
run;

/* Example Log Output:
WARNING: var1 is missing for _N_=34
*/

Reviewing the log helps catch potential issues early, ensuring that your program runs smoothly and produces accurate results.

4. Use PROC COMPARE to Validate Data Consistency

PROC COMPARE is a powerful procedure for comparing two datasets to ensure they match. This is particularly useful for QC when you have a reference dataset or an independently generated dataset to compare against.

Example: After creating a summary dataset, use PROC COMPARE to validate it against a reference dataset to ensure that all values match as expected.

/* Example: Using PROC COMPARE to validate datasets */
proc compare base=refdata compare=qcdata;
   id subjectid visit;
   run;

In this example, PROC COMPARE checks if the dataset qcdata matches the reference dataset refdata for each subject and visit. Any differences are reported in the output, allowing you to identify and correct inconsistencies.

5. Implement Defensive Programming Techniques

Defensive programming involves writing code that anticipates and handles potential errors or unexpected input. This approach can prevent issues from occurring in the first place and make the QC process smoother.

Example: Include checks for missing data, ensure that key variables are present, and handle edge cases such as divisions by zero or unexpected data types.

/* Example: Defensive programming to handle missing data */
data validated;
   set rawdata;
   if missing(age) then do;
      put "WARNING: Missing age for " subjectid=;
      age = .;
   end;
   if age < 0 then do;
      put "ERROR: Negative age found for " subjectid=;
      age = .;
   end;
run;

In this example, the program checks for missing or negative values in the age variable, logs warnings and errors to the SAS log, and ensures that the data is handled appropriately.

6. Create Test Cases for Key Code Sections

Testing individual sections of your code with specific test cases can help ensure that each part of the program is working as expected. These tests should cover both typical cases and edge cases to ensure robustness.

Example: If your code includes a function to calculate BMI, create test cases with various height and weight values, including extreme values, to ensure the function handles all cases correctly.

/* Example: Test cases for BMI calculation */
data testcases;
   input height weight;
   bmi = weight / (height/100)**2;
   put "BMI=" bmi;
   datalines;
180 75
160 100
150 45
0 70  /* Edge case: height=0 */
;
run;

In this example, the program calculates BMI for a range of test cases, including an edge case where height is zero, helping you verify that the BMI calculation handles all scenarios correctly.

7. Use PUTLOG for Debugging

PUTLOG is a valuable debugging tool that allows you to print specific information to the log during data step execution. This can be particularly helpful when QCing complex data manipulations or when trying to understand the flow of the program.

Example: Use PUTLOG to output key variable values and the current iteration of a loop, helping you trace the program's execution and identify where things may go wrong.

/* Example: Using PUTLOG for debugging */
data validated;
   set rawdata;
   if age < 18 then do;
      putlog "NOTE: Minor found with age=" age " for " subjectid=;
   end;
   if bmi > 30 then putlog "ALERT: High BMI=" bmi " for " subjectid=;
run;

In this example, PUTLOG is used to print messages to the log whenever a minor is identified or when a subject has a high BMI, providing a clear trace of how the program is processing the data.

8. Cross-Check Output Formats

Ensure that the output datasets, tables, and figures are formatted correctly according to the study’s specifications. This includes checking for correct variable labels, formats, and consistent presentation of results.

Example: If the output includes a table with mean values, ensure that the values are rounded correctly and that the table format (e.g., column headers, alignment) meets the required standards.

/* Example: Ensuring consistent output formats */
proc print data=summarydata noobs label;
   var subjectid visit meanvalue;
   format meanvalue 8.2;
   label meanvalue = "Mean Value (units)";
run;

This example shows how to ensure that the meanvalue variable is formatted with two decimal places and labeled correctly in the output.

9. Version Control and Documentation

Maintain version control of your programs and datasets, and document all changes thoroughly. This practice helps ensure that you can track what changes were made, why they were made, and who made them.

Example: Use version control software like Git to track changes and ensure that each version of your code is documented with clear commit messages.

git init
git add program.sas
git commit -m "Initial version of summary statistics program"
git commit -am "Fixed issue with missing values in age calculation"

In this example, Git is used to initialize a repository, add the SAS program, and commit changes with descriptive messages, helping maintain a clear history of code development.

10. Peer Review and Collaborative QC

Involve a colleague in the QC process by having them review your code or independently reproduce your results. A fresh pair of eyes can often spot issues that the original programmer may overlook.

Example: After completing your QC, ask a colleague to review your program and provide feedback. If possible, they can run an independent program to cross-verify your results.

/* Example: Collaborative QC */
data qcdata;
   set studydata;
   /* Independent calculation or check */
run;

/* Colleague can review or run their own checks on qcdata */

11. Automate QC Processes Where Possible

Automate repetitive QC tasks to save time and reduce human error. This could include creating scripts that automatically compare datasets, check for missing values, or verify that certain criteria are met.

Example: Automate the comparison of datasets using PROC COMPARE or create a macro that checks for missing values across all variables in a dataset.

%macro check_missing(data=);
   proc means data=&data. nmiss;
      var _numeric_;
   run;
%mend check_missing;

/* Example usage */
%check_missing(data=studydata);

In this example, a macro is created to automate the process of checking for missing values in a dataset, making it easier to perform QC across multiple datasets.

12. Conduct Final End-to-End Testing

Once individual sections of the program have been QC'd, conduct a final end-to-end test of the entire program. This ensures that the complete process works as expected and that all outputs are accurate.

Example: After making revisions based on the QC process, run the entire SAS program from start to finish, and compare the final output with expected results or reference data to ensure everything is correct.

/* Example: Final end-to-end test */
data finaloutput;
   set studydata;
   /* Full program logic here */
run;

proc compare base=finaloutput compare=expected_output;
   id subjectid visit;
run;

This example demonstrates how to perform a final end-to-end test by running the entire program and comparing the final output to expected results using PROC COMPARE.

13. Maintain a QC Checklist

Develop and maintain a QC checklist that includes all the steps required to thoroughly QC a SAS program. This ensures that no critical steps are overlooked and provides a standardized approach to QC across different projects.

Example: Your QC checklist might include items like "Review SAS log," "Check variable labels and formats," "Run independent program for comparison," and "Verify final outputs against specifications."

/* Example: QC Checklist */
- Review SAS log for errors, warnings, and notes
- Validate datasets using PROC COMPARE
- Cross-check output formats and labels
- Perform independent QC programming
- Conduct end-to-end testing
- Document all changes and maintain version control

By following these best practices and utilizing the provided examples, you can ensure that your SAS programs are thoroughly QC'd and produce reliable, accurate results. Implementing these strategies will enhance the quality of your work and help avoid potential errors that could impact the outcome of your analysis.

>SDTM (Study Data Tabulation Model) programming is a crucial aspect of clinical trial data management

SDTM (Study Data Tabulation Model) programming is a crucial aspect of clinical trial data management, ensuring that data is standardized, traceable, and ready for regulatory submission. Below are some practical tips for SDTM programming, complete with specific examples and code snippets to help you manage your clinical data more efficiently and effectively.

1. Understand the SDTM Implementation Guide (IG)

The SDTM IG is your primary reference when working with SDTM datasets. It provides detailed guidelines on how to structure and standardize your data. Familiarize yourself with the requirements for each domain, including the use of controlled terminology, dataset structures, and relationships between domains.

Example: When creating the AE (Adverse Events) domain, ensure you include required variables like USUBJID, AEDECOD, AESTDTC, and AESEV. Reference the IG to determine how these variables should be populated and linked to other domains.

2. Use Controlled Terminology Consistently

Controlled terminology is essential for consistency across datasets. Always use the latest controlled terminology standards provided by CDISC. This includes coding variables like AEDECOD using MedDRA and CMDECOD (Concomitant Medication Dictionary-Derived Term) using WHO Drug.

Example: If coding a concomitant medication, ensure that CMTRT (reported term for the treatment) and CMDECOD are aligned with the WHO Drug dictionary to maintain consistency.

data cm;
   set raw_cm;
   length CMDECOD $40;
   if cmtrt = 'aspirin' then cmdecod = 'Aspirin';
   else if cmtrt = 'acetaminophen' then cmdecod = 'Acetaminophen';
   /* Additional coding logic here */
run;

3. Leverage the Power of SAS Macros

SAS macros can automate repetitive tasks and ensure consistency across datasets. For example, you can create a macro to standardize date variables across all domains or to generate common SUPPQUAL datasets.

Example: The macro below standardizes date variables across multiple domains, ensuring they are in ISO 8601 format.

%macro standardize_dates(data=, datevar=);
   data &data.;
      set &data.;
      format &datevar. yymmdd10.;
      &datevar. = input(put(&datevar., yymmdd10.), yymmdd10.);
   run;
%mend standardize_dates;

/* Example usage */
%standardize_dates(data=ae, datevar=aestdtc);
%standardize_dates(data=cm, datevar=cmstdtc);

4. Validate Your Data Early and Often

Validation is key to ensuring that your SDTM datasets are compliant with regulatory standards. Use tools like Pinnacle 21 to validate your datasets against the SDTM IG. Validate early in the process to catch errors before they become embedded in your datasets.

Example: Use Pinnacle 21 to run a validation report on your SDTM datasets to identify and correct issues such as missing variables, incorrect data types, or violations of controlled terminology.

5. Document Your Work Thoroughly

Good documentation is essential for traceability and reproducibility. Keep detailed records of your data transformations, including the source data, the steps taken to convert it into SDTM format, and any issues encountered.

Example: Use comments in your SAS code to document complex logic or assumptions. Also, ensure that your define.xml file includes all necessary metadata.

/* Example: Documenting a derived variable in AE domain */
data ae;
   set raw_ae;
   /* Deriving AE duration */
   aedur = aendt - aestdt;
   /* Assuming events with missing end date are ongoing */
   if missing(aendt) then aedur = .;
run;

6. Pay Attention to Date/Time Variables

Date and time variables can be tricky to handle, especially when dealing with partial or missing data. Always use ISO 8601 format (e.g., YYYY-MM-DD) for date and time variables. Be consistent in how you handle missing components and ensure that all date/time variables are correctly formatted.

Example: When creating the DM (Demographics) domain, ensure that birth date (BRTHDTC) and informed consent date (RFICDTC) are formatted according to ISO 8601 standards.

data dm;
   set raw_dm;
   format brthdtc rficdtc yymmdd10.;
   brthdtc = input(put(birth_date, yymmdd10.), yymmdd10.);
   rficdtc = input(put(consent_date, yymmdd10.), yymmdd10.);
run;

7. Use RELREC to Maintain Relationships Between Records

The RELREC domain is crucial for maintaining relationships between different datasets, such as linking adverse events with concomitant medications.

Example: To link an adverse event to a concomitant medication that was administered at the same time, you would use the RELREC domain.

data relrec;
   length USUBJID RDOMAIN IDVAR IDVARVAL RELTYPE RELID $20;
   set ae cm;
   if ae.usubjid = cm.usubjid and aestdtc = cmstdtc then do;
      rdomain = "AE";
      idvar = "AESEQ";
      idvarval = put(aeseq, best.);
      reltype = "ONE";
      relid = "AE_CM";
      output;

      rdomain = "CM";
      idvar = "CMSEQ";
      idvarval = put(cmseq, best.);
      reltype = "ONE";
      relid = "AE_CM";
      output;
   end;
run;

8. Handle SUPPQUAL Carefully

The SUPPQUAL domain is used for supplemental qualifiers that do not fit into the standard SDTM domains. Ensure that the IDVAR and IDVARVAL correctly reference the parent domain and that the supplemental data is necessary and compliant with the SDTM IG.

Example: The code below shows how to add a supplemental qualifier for the AE domain to capture toxicity grade information.

data suppae;
   set ae;
   if aetoxgr ne '' then do;
      rdomain = "AE";
      idvar = "AESEQ";
      idvarval = put(aeseq, best.);
      qnam = "AETOXGR";
      qlabel = "Toxicity Grade";
      qval = aetoxgr;
      output;
   end;
run;

9. Stay Updated with CDISC Guidelines

CDISC guidelines and controlled terminology are periodically updated. Make sure you stay informed about these updates and apply them to your SDTM datasets.

Example: Regularly check the CDISC website for updates on controlled terminology. Implement these updates in your SAS programs to ensure your datasets remain compliant.

10. Test Your Code with Small, Representative Datasets

Before applying your code to the full dataset, test it on a smaller, representative sample. This helps identify any potential issues without processing the entire dataset.

Example: Create a small dataset with representative data and run your SDTM conversion code on it. Verify that the output matches your expectations before processing the entire dataset.

data ae_sample;
   set ae(obs=100); /* Testing with the first 100 records */
run;

/* Apply your SDTM conversion code to ae_sample */

11. Use ODS for Creating Define.xml Files

The define.xml file is critical for your SDTM submission. SAS’s ODS (Output Delivery System) can be used to create the define.xml file, ensuring it is properly formatted and compliant with regulatory requirements.

Example: Use the following SAS code to generate the define.xml file, including all necessary metadata and controlled terminology references.

ods cdisc define file="define.xml" style=sasweb;
proc cdisc model=sdtm;
   define metadata="define_metadata.sas7bdat";
   read definedata metadata="define_metadata.sas7bdat";
   write;
run;
ods cdisc close;

12. Maintain Traceability from Raw Data to SDTM

Traceability is essential for demonstrating the accuracy and integrity of your SDTM datasets. Ensure that every variable in your SDTM datasets can be traced back to the original raw data.

Example: Document each transformation step in your SAS code and ensure it is clearly explained in the define.xml file.

/* Example: Documenting the derivation of visit number in VS domain */
data vs;
   set raw_vs;
   /* Deriving VISITNUM from raw visit name */
   if visit = 'Baseline' then visitnum = 1;
   else if visit = 'Week 1' then visitnum = 2;
   /* Additional visit logic here */
run;

/* Ensure that the define.xml file includes details about this derivation */

13. Manage Version Control Effectively

Use version control software like Git to track changes to your SDTM datasets and SAS programs. This allows you to revert to previous versions if necessary and ensures that you have a complete history of all changes made.

Example: Set up a Git repository for your SDTM project and commit changes regularly. Include clear commit messages that describe the changes made.

git init
git add .
git commit -m "Initial commit of SDTM conversion programs"
git commit -am "Updated AE domain to include new controlled terminology"

14. Optimize Performance for Large Datasets

When working with large datasets, performance can become an issue. Optimize your SAS code by using efficient data step and PROC SQL techniques.

Example: Minimize the number of data passes by combining multiple operations in a single data step or PROC SQL query. Avoid unnecessary sorting or merging.

proc sql;
   create table ae_final as
   select a.*, b.cmtrt
   from ae as a
   left join cm as b
   on a.usubjid = b.usubjid
   where a.aesev = 'SEVERE' and b.cmtrt = 'Steroid';
quit;

15. Collaborate with Team Members

SDTM programming is often a collaborative effort. Regularly communicate with your team members to ensure that everyone is aligned on the standards and processes being used.

Example: Use shared code repositories, regular meetings, and clear documentation to facilitate collaboration and ensure consistency across the team’s work.

16. Prepare for Audits

Regulatory audits can happen at any time, so it's important to be prepared. Ensure that all your datasets, programs, and documentation are organized and accessible.

Example: Regularly review your work to ensure compliance with SDTM standards. Create a checklist of key compliance points and review it before submitting any data.

17. Utilize PROC CDISC

PROC CDISC in SAS is a powerful tool for creating SDTM datasets that comply with CDISC standards. Familiarize yourself with PROC CDISC options and use it to validate and generate SDTM datasets efficiently.

Example: Use PROC CDISC to read and validate your SDTM datasets, ensuring that they meet the required standards.

proc cdisc model=sdtm;
   define metadata="define_metadata.sas7bdat";
   read definedata metadata="define_metadata.sas7bdat";
   write;
run;

18. Stay Organized with Project Files

Keep your project files well-organized by maintaining separate directories for raw data, SDTM datasets, programs, logs, and outputs.

Example: Use a clear directory structure like the one below to keep your files organized and easy to find:

/SDTM_Project
   /raw_data
   /sdtm_datasets
   /programs
   /logs
   /outputs

19. Understand the Importance of Domain-Specific Variables

Each SDTM domain has specific variables that must be included. Ensure that you understand the purpose of these variables and that they are correctly populated.

Example: In the LB (Laboratory) domain, ensure that variables like LBTESTCD, LBORRES, and LBNRIND (Reference Range Indicator) are accurately populated.

data lb;
   set raw_lb;
   if lbtestcd = 'GLUC' then do;
      lborres = glucose_value;
      lbunit = 'mg/dL';
      if lborres > 110 then lbnrind = 'HIGH';
      else if lborres < 70 then lbnrind = 'LOW';
      else lbnrind = 'NORMAL';
   end;
run;

20. Engage in Continuous Learning

The field of clinical data management is constantly evolving. Engage in continuous learning by attending webinars, participating in CDISC workshops, and networking with other SDTM programmers.

Example: Subscribe to newsletters from CDISC and SAS, attend industry conferences, and join professional organizations to stay informed about the latest trends and best practices in SDTM programming.

By following these SDTM programming tips, you can ensure that your clinical trial data is well-structured, compliant with regulatory standards, and ready for submission. Effective SDTM programming not only facilitates smoother regulatory review but also contributes to the overall success of the clinical trial.

Summary of Key Differences between each SDTM IG versions

Comparison of SDTM Implementation Guide (IG) Versions: 3.1.1 vs 3.1.2 vs 3.1.3 vs 3.2 vs 3.3 vs 3.4

The Study Data Tabulation Model (SDTM) Implementation Guide (IG) is updated periodically to incorporate new standards and improve existing ones. Below is a comparison of the key differences and updates across the SDTM IG versions from 3.1.1 to 3.4.

SDTM IG 3.1.1

• Initial Introduction: SDTM IG 3.1.1 was one of the earlier versions that laid the foundation for standardizing clinical trial data for regulatory submissions.
• Core Domains: Introduced essential domains like DM (Demographics), AE (Adverse Events), and LB (Laboratory), which became the standard for clinical trial data submission.
• Basic Structure: Established the general structure for SDTM domains, including the use of standardized variable names and controlled terminology.

SDTM IG 3.1.2

• Minor Revisions: SDTM IG 3.1.2 included minor updates and clarifications to existing standards without introducing significant changes.
• Additional Controlled Terminology: Enhanced the controlled terminology lists, improving consistency and standardization across datasets.
• Introduction of New Domains: Introduced new domains such as SC (Subject Characteristics) and MS (Microbiology Specimen), expanding the range of supported data types.

SDTM IG 3.1.3

• Clarifications and Corrections: Addressed ambiguities in the previous versions, providing clearer guidelines on specific domains and variables.
• New Variables: Added new variables in existing domains to capture more detailed information, such as AESEV (Adverse Event Severity) in the AE domain.
• Enhanced Metadata Documentation: Improved the requirements for metadata documentation, emphasizing the importance of the define.xml file.

SDTM IG 3.2

• Significant Updates: SDTM IG 3.2 introduced several new domains and revised existing ones, reflecting the evolving needs of clinical trial data management.
• New Domains: Introduced key domains such as MB (Microbiology), TU (Tumor Identification), TR (Tumor Response), and RS (Response Evaluation), particularly for oncology studies.
• Standardization of Date/Time Variables: Improved standardization for handling date and time variables across domains.
• Introduction of Supplemental Domains: Expanded the use of the SUPP-- (Supplemental Qualifiers) structure to accommodate non-standard data.

SDTM IG 3.3

• Further Domain Expansion: SDTM IG 3.3 introduced additional domains, particularly focused on new therapeutic areas and specific types of clinical data.
• New Domains: Added domains like DD (Death Details), DI (Device In-Use), and RELREC (Related Records) for better data linkage and tracking.
• Refinement of Oncology Domains: Enhanced the oncology-specific domains introduced in IG 3.2, such as TU, TR, and RS, to better capture complex oncology data.
• Improved Examples and Guidance: Provided more detailed examples and guidance on how to implement the standards in various clinical scenarios.

SDTM IG 3.4

• Latest Enhancements: SDTM IG 3.4 is the most recent version, incorporating feedback from previous implementations and further refining the standards.
• New and Updated Domains: Introduced new domains like QS (Questionnaires) and improved existing ones, particularly in the area of device data and pharmacogenomics.
• Digital Health Data: Added guidance on handling digital health data, reflecting the increasing use of digital devices in clinical trials.
• Increased Emphasis on Traceability: Enhanced focus on ensuring traceability from source data to SDTM datasets, emphasizing the importance of clear documentation and metadata.
• Additional Controlled Terminology: Expanded the controlled terminology lists to include new terms relevant to emerging therapeutic areas.

Summary of Key Differences

The evolution of the SDTM Implementation Guide from version 3.1.1 to 3.4 reflects the growing complexity of clinical trials and the need for more detailed and standardized data capture. Each version has built on the previous ones, introducing new domains, refining existing ones, and expanding the use of controlled terminology. The most recent versions, particularly 3.3 and 3.4, have focused on oncology data, device data, and the incorporation of digital health data, ensuring that SDTM remains relevant in the face of technological advancements in clinical research.

As the SDTM IG continues to evolve, it is crucial for clinical programmers to stay updated on the latest standards and best practices to ensure compliance and maintain the integrity of clinical trial data.

Detailed Comparison of SDTM IG Versions 3.2 vs 3.3 vs 3.4

The Study Data Tabulation Model (SDTM) Implementation Guide (IG) is periodically updated to reflect advancements in clinical research and to incorporate feedback from its use in regulatory submissions. This report highlights the key differences and updates between SDTM IG versions 3.2, 3.3, and 3.4, with specific examples to illustrate these changes.

SDTM IG Version 3.2

• Introduction of Oncology Domains: Version 3.2 marked a significant update with the introduction of domains specific to oncology studies:
  • TU (Tumor Identification): Used to identify and categorize tumors.
    • Example: The TU domain includes variables like TUSTRESC (Tumor Identification Standardized Result) and TULOC (Tumor Location), which were not present in earlier versions.
  • TR (Tumor Response): Captures tumor response assessments.
    • Example: The TR domain introduced variables such as TRTESTCD (Tumor Response Test Code) and TRORRES (Tumor Response Original Result) to record response details, like partial response or progressive disease.
  • RS (Response Evaluation): Used for recording the overall response evaluation, particularly in oncology trials.
    • Example: The RS domain includes variables like RSORRES (Response Evaluation Original Result) to capture overall response such as "Complete Response" or "Stable Disease".
• New Domains: Several new domains were introduced, including:
  • MB (Microbiology): Captures microbiological data.
    • Example: The MB domain introduced variables like MBTESTCD (Microbiology Test Code) and MBORRES (Microbiology Original Result), allowing for detailed tracking of microbiological findings such as bacterial culture results.
  • MS (Microscopic Findings): Records findings from microscopic examinations.
    • Example: Variables such as MSTESTCD (Microscopic Test Code) and MSORRES (Microscopic Original Result) were introduced to capture detailed histopathological results.
  • PR (Procedures): Captures information about medical procedures performed during the study.
    • Example: The PR domain included variables like PRTRT (Procedure Name) and PRSTDTC (Procedure Start Date/Time) to document surgical interventions and other procedures.
  • RELREC (Related Records): Establishes relationships between records in different domains.
    • Example: The RELREC domain was enhanced to support complex relationships between datasets, such as linking an adverse event with a concomitant medication record.
• Standardization of Date/Time Variables: Version 3.2 improved the standardization of date and time variables across domains, using ISO 8601 formats for consistency.
  • Example: Variables like --STDTC (Start Date/Time) and --ENDTC (End Date/Time) were standardized to ensure uniform reporting of temporal data.
• Enhanced Metadata Documentation: Emphasized the importance of comprehensive metadata documentation, particularly in the define.xml file, to ensure data traceability and clarity.
  • Example: The define.xml file became more robust in version 3.2, with improved requirements for documenting variable derivations, controlled terminology, and value-level metadata.

SDTM IG Version 3.3

• Further Expansion of Oncology Domains: Building on the oncology domains introduced in version 3.2, version 3.3 further refined these domains, particularly for more complex oncology data:
  • Expanded definitions and examples for TU, TR, and RS domains to better accommodate the variety of tumor assessments and responses encountered in oncology trials.
    • Example: Version 3.3 included additional guidance on managing longitudinal tumor data, such as handling changes in tumor location or size over multiple assessments.
• Introduction of New Domains: Version 3.3 added several new domains to cover additional clinical data types:
  • DD (Death Details): Captures detailed information about the circumstances and cause of death.
    • Example: The DD domain introduced variables like DDTESTCD (Death Test Code) and DDORRES (Death Original Result), allowing for detailed documentation of death-related events, such as "Sudden Cardiac Death."
  • DI (Device In-Use): Records data about medical devices used during the study.
    • Example: The DI domain introduced variables like DITESTCD (Device Test Code) and DIORRES (Device Original Result), capturing information about device usage, functionality, and related findings.
  • RP (Reproductive System Findings): Captures findings related to the reproductive system.
    • Example: The RP domain includes variables like RPTESTCD (Reproductive Test Code) and RPORRES (Reproductive System Original Result), capturing data from reproductive health assessments, such as fertility evaluations or pregnancy outcomes.
• Device Data Standardization: The DI (Device In-Use) domain was introduced to accommodate data related to medical devices, reflecting the growing use of devices in clinical trials.
  • Example: The DI domain included specific guidance on documenting device malfunctions, interventions, and outcomes, ensuring that all device-related data is captured consistently across studies.
• Refinement of Existing Domains: Version 3.3 included updates to existing domains, with more detailed guidance and examples provided to improve consistency and accuracy in data submission.
  • Clarified usage of the SUPPQUAL domain for supplemental qualifiers, ensuring that non-standard variables are correctly linked to their parent domains.
    • Example: Enhanced the documentation for how to properly use QNAM and QLABEL in the SUPPQUAL domain to maintain data consistency and traceability.
  • Enhanced the guidance for the use of RELREC (Related Records) domain to better manage complex relationships between different data points.
    • Example: Provided examples of how to link related records across different domains, such as linking an ECG result with a concurrent medication record in CM.
• Expanded Controlled Terminology: Version 3.3 further expanded the controlled terminology lists, ensuring that emerging clinical data types are adequately captured and standardized.
  • Example: New terms were added to capture advanced diagnostics and treatment modalities, such as immunotherapies and next-generation sequencing results.

SDTM IG Version 3.4

• Focus on Digital Health and Wearable Data: Reflecting the increased use of digital health technologies, version 3.4 introduced new guidance on handling data from wearable devices and other digital health technologies:
  • Guidance on incorporating digital health data into existing SDTM domains or creating new domains where necessary.
    • Example: Provided guidelines for integrating continuous glucose monitoring data into the LB (Laboratory) domain, including how to handle high-frequency data points.
• Introduction of New Domains and Updates: Version 3.4 continued the trend of expanding and refining SDTM domains:
  • QS (Questionnaires): Expanded to include more detailed guidelines for handling complex questionnaire data, especially in therapeutic areas like mental health.
    • Example: The QS domain now includes guidance on managing multi-part questionnaires, where different sections may have different scaling or scoring methods.
  • DD (Death Details): Refined to capture even more detailed data on death events, including timing relative to study treatment and follow-up periods.
    • Example: Enhanced documentation on how to capture death events that occur during long-term follow-up, ensuring that the context of the death (e.g., treatment-related, post-treatment) is clearly documented.
• Enhanced Traceability: Version 3.4 emphasized the importance of traceability from source data to SDTM datasets, providing more detailed guidance on maintaining clear and consistent documentation throughout the data lifecycle:
  • Included additional requirements for metadata and define.xml files to improve the transparency and traceability of data transformations.
    • Example: Provided specific examples on documenting derivations in define.xml, ensuring that each variable’s origin and transformation process are fully transparent to reviewers.
• Further Refinement of Oncology Domains: Continued to refine oncology-specific domains (TU, TR, RS) to ensure they meet the needs of increasingly complex oncology trials:
  • Improved guidance on managing tumor response data, particularly in studies involving multiple treatment lines or combination therapies.
    • Example: Updated the TR domain with guidance on how to handle tumor response in cases of crossover study designs or when a subject receives multiple therapies sequentially.
• Expanded Guidance on Controlled Terminology: Further expanded controlled terminology to include new terms relevant to emerging therapeutic areas and technologies.
  • Example: Added terms related to digital biomarkers, pharmacogenomics, and other advanced therapeutic areas to ensure that these data types can be standardized across studies.

Summary of Key Differences Between Versions 3.2, 3.3, and 3.4

Each subsequent version of the SDTM IG has built upon the previous one, introducing new domains, refining existing ones, and expanding the scope to accommodate the latest trends in clinical research. The examples provided illustrate the specific changes and enhancements that have been made in each version.

• Version 3.2: Focused on introducing new domains, particularly for oncology studies, and improving standardization across datasets. Introduced key oncology domains and improved standardization of date/time variables.
• Version 3.3: Expanded the range of domains, particularly for device data and reproductive system findings, and further refined the oncology-specific domains introduced in 3.2. Also introduced detailed guidance on the use of supplemental qualifiers and related records.
• Version 3.4: Emphasized digital health and wearable data, enhanced traceability, and continued to refine oncology domains, making it the most comprehensive and up-to-date version. Focused on the integration of digital health data and the further expansion of controlled terminology.

As clinical research evolves, the SDTM IG will continue to be updated to ensure that it remains relevant and useful for capturing the increasingly complex data generated by modern clinical trials.