Advanced SDTM Programming Techniques for SAS Programmers

As an experienced SAS programmer working with the Study Data Tabulation Model (SDTM), it's crucial to stay updated with the latest programming techniques. Whether you're tasked with building SDTM domains from scratch or optimizing existing code, there are several advanced concepts that can improve your workflows and output. In this post, we’ll explore some techniques that can help you overcome common SDTM challenges and boost efficiency in handling clinical trial data.

1. Efficient Handling of Large Datasets

When dealing with large clinical datasets, speed and efficiency are key. One method to optimize SDTM domain generation is to reduce the data footprint by eliminating unnecessary variables and duplicative observations. Consider the following approaches:

Removing Duplicate Observations

Duplicate records can slow down the processing of datasets and cause inaccuracies in reporting. To remove duplicates, you can use the PROC SQL, DATA STEP, or PROC SORT methods. Here's a quick example using PROC SORT:

proc sort data=mydata nodupkey;
    by usubjid visitnum;
run;

This example ensures that only unique records for each usubjid and visitnum combination are retained, eliminating potential redundancy in your dataset.

2. Mastering Macro Variables for Flexibility

Utilizing macro variables efficiently can significantly improve your code's flexibility, especially when working across multiple SDTM domains. Macro variables allow you to automate repetitive tasks and reduce the risk of human error. Here’s an example using macro variables to generate domain-specific reports:

%macro create_sdtm_report(domain);
    data &domain._report;
        set sdtm.&domain.;
        /* Apply domain-specific transformations */
    run;
%mend;

%create_sdtm_report(DM);
%create_sdtm_report(LB);

In this case, the macro dynamically generates SDTM reports for any domain by passing the domain name as a parameter, minimizing manual interventions.

3. Managing Demographics with the DC Domain

The Demographics as Collected (DC) domain often presents unique challenges, particularly when distinguishing it from the standard Demographics (DM) domain. While DM represents standardized data, DC focuses on the raw, collected demographic details. Here's an approach to manage these domains efficiently:

data dc_domain;
    set raw_data;
    /* Capture specific collected demographic data */
    where not missing(collected_age) and not missing(collected_gender);
run;

In this case, the code filters out any missing collected data to ensure the DC domain contains only records with complete demographic information.

4. Debugging SDTM Code with PUTLOG

Efficient debugging is crucial, especially when dealing with complex SDTM transformations. The PUTLOG statement in SAS is a simple yet powerful tool for tracking errors and debugging data issues.

data check;
    set sdtm.dm;
    if missing(usubjid) then putlog "ERROR: Missing USUBJID at obs " _n_;
run;

In this example, the PUTLOG statement flags records where the USUBJID variable is missing, making it easier to spot and address data quality issues during SDTM creation.

5. Advanced Array Processing for Repeated Measures

In certain domains like Vital Signs (VS) or Lab (LB), handling repeated measures for patients across multiple visits is common. Using arrays can help streamline this process. Here’s a basic example of using an array to process repeated lab measurements:

data lab_repeated;
    set sdtm.lb;
    array lb_vals{3} lbtestcd1-lbtestcd3;
    do i=1 to dim(lb_vals);
        if lb_vals{i} = . then putlog "WARNING: Missing value for LBTESTCD at " _n_;
    end;
run;

This code uses an array to loop through repeated lab test results, ensuring that missing values are flagged for review. Such array-based techniques are essential when processing large, multidimensional datasets in SDTM programming.

6. Best Practices for CDISC Validation and Compliance

To ensure SDTM datasets are CDISC compliant, it’s vital to validate your datasets using tools like Pinnacle 21 or OpenCDISC. These tools check compliance against SDTM standards, flagging any inconsistencies or issues.

Make sure to incorporate validation steps into your workflows regularly. This can be done by running the validation tool after each major dataset creation and by including clear annotations in your programming code to ensure traceability for audits.

Conclusion

Advanced SDTM programming requires a mix of technical expertise and strategic thinking. Whether you are optimizing large datasets, automating repetitive tasks with macros, or ensuring CDISC compliance, staying updated with these advanced techniques will enhance your efficiency and ensure high-quality deliverables in clinical trials. Remember, SDTM programming is not just about writing code—it's about delivering accurate, compliant, and reliable data for critical decision-making.

For more tips and tutorials, check out other PharmaSUG resources or SAS support!

Excluding Variables in PROC COMPARE Using the DROP Dataset Option

When comparing two datasets using PROC COMPARE in SAS, there may be cases where you want to exclude specific variables from the comparison. One efficient way to do this is by using the DROP dataset option. This option allows you to remove certain variables from consideration during the comparison process.

Using the DROP Dataset Option in PROC COMPARE

The DROP dataset option is applied directly to the dataset references in the BASE and COMPARE options of PROC COMPARE. When you use DROP, the specified variables are excluded from the comparison.

Here is the syntax for using the DROP option:

proc compare base=dataset1(drop=var_to_exclude) 
                compare=dataset2(drop=var_to_exclude);
    run;

In this example, var_to_exclude is the name of the variable you want to exclude from both datasets (dataset1 and dataset2) before the comparison is made.

Example: Excluding a Variable from the Comparison

Let's say you have two datasets, sales_2023 and sales_2024, and you want to compare them, but you want to exclude a variable called region from the comparison. Here is how you can do that:

proc compare base=sales_2023(drop=region) 
                compare=sales_2024(drop=region);
    run;

This code ensures that the variable region will not be included in the comparison, while all other variables in the two datasets will be compared.

Excluding Multiple Variables

You can also exclude multiple variables by listing them inside the DROP option, separated by spaces:

proc compare base=sales_2023(drop=region quarter) 
                compare=sales_2024(drop=region quarter);
    run;

In this case, both region and quarter will be excluded from the comparison.

Conclusion

The DROP dataset option is a powerful and simple tool for excluding variables from comparison in PROC COMPARE. It provides flexibility by allowing you to exclude one or more variables without needing to manually specify which variables should be included in the comparison.

By leveraging the DROP option, you can ensure that only relevant variables are considered when comparing datasets, making your SAS programming more efficient and focused.

Mastering PROC SQL: Best Practices for Seamlessly Adding Columns

PROC SQL in SAS is a powerful tool, especially when you need to add new columns to existing tables. Whether you're updating tables with new data or making calculated columns, following best practices ensures your code is efficient, maintainable, and easy to understand.

1. Use ALTER TABLE to Add Columns

The most straightforward way to add a column to a table is by using the ALTER TABLE statement. It allows you to add one or more columns without disrupting the existing data structure.

proc sql;
   alter table your_table_name
   add new_column1 num format=8. label='New Column 1',
       new_column2 char(20) label='New Column 2';
quit;

Tip: Always specify the data type (num for numeric, char(n) for character) and add useful formats and labels to keep your columns clear and organized.

2. Avoid Duplicating Column Names

Before adding columns, check if a column with the same name already exists. SAS will throw an error if you try to add a column that’s already in the table.

proc sql;
   describe table your_table_name;
quit;

Use the DESCRIBE statement to view the structure and ensure no duplicate column names exist.

3. Efficient Data Insertion

After adding a column, you may need to initialize it with default values. You can do this in a single step using the UPDATE statement to ensure all rows are filled efficiently:

proc sql;
   alter table your_table_name
   add new_column num format=8. label='New Numeric Column';

   update your_table_name
   set new_column = 0; /* Default value */
quit;

4. Use CREATE TABLE for Complex Column Addition

If you need to add columns based on existing data or calculations, it's often better to create a new table. This prevents any unintentional changes to the original table and allows for cleaner manipulation of data:

proc sql;
   create table new_table as
   select existing_column,
          calculated(new_column) as new_column format=8. label='Calculated Column'
   from your_table_name;
quit;

Pro Tip: Use calculated to create columns based on other columns in the same query.

5. Consider Indexing for Performance

If the new column will be used in sorting or joins, adding an index can significantly boost performance. Here’s how to create an index on your new column:

proc sql;
   create index idx_new_column on your_table_name(new_column);
quit;

6. Use Comments for Clarity

Always document your SQL code! Adding comments helps future developers (or your future self) understand the purpose of each column addition:

proc sql;
   alter table your_table_name
   add new_column char(10) label='New Character Column'; /* Added for analysis purposes */
quit;

Final Thoughts

By following these best practices for adding columns via PROC SQL, you can keep your SAS programming efficient and clear. Whether you're maintaining legacy systems or building new reports, these tips will help you seamlessly integrate new data into your tables.

How to Address PROC COMPARE Reporting Same Values as Different in SAS

Working with large datasets in SAS often requires comparing data between two tables. The PROC COMPARE procedure is an essential tool for this task, but sometimes it reports values as different even when they appear to be identical. This issue can arise from various causes, such as numeric precision differences, rounding issues, or formatting inconsistencies. In this post, we will explore common causes of this issue and how to resolve them.

1. Numeric Precision Issues

SAS stores numeric values using floating-point precision, which can lead to small differences that aren't immediately visible. These differences may cause PROC COMPARE to report discrepancies even though the values seem the same.

Solution: Use the CRITERION or FUZZ option to define an acceptable tolerance for differences.

proc compare base=dataset1 compare=dataset2 criterion=0.00001;
run;
    

2. Rounding Differences

If values have been rounded differently in two datasets, PROC COMPARE may detect them as different. For example, one dataset may round to two decimal places, while the other doesn't.

Solution: Apply consistent rounding to both datasets before comparison.

data dataset1_rounded;
    set dataset1;
    value = round(value, 0.01); /* Round to two decimal places */
run;

data dataset2_rounded;
    set dataset2;
    value = round(value, 0.01); /* Same rounding precision */
run;

proc compare base=dataset1_rounded compare=dataset2_rounded;
run;
    

3. Formatting Differences

Sometimes, two values are the same but have different formats applied, leading to a perceived difference by PROC COMPARE.

Solution: Use the NOFORMAT option to ignore formatting in the comparison.

proc compare base=dataset1 compare=dataset2 noformat;
run;
    

4. Character Value Differences (Case Sensitivity and Whitespace)

SAS is case-sensitive when comparing character variables. Extra whitespace at the end of strings can also cause PROC COMPARE to flag a difference.

Solution: Standardize case and remove any unnecessary spaces using the COMPRESS or UPCASE functions.

data dataset1_clean;
    set dataset1;
    char_var = compress(upcase(char_var));
run;

data dataset2_clean;
    set dataset2;
    char_var = compress(upcase(char_var));
run;

proc compare base=dataset1_clean compare=dataset2_clean;
run;
    

5. Handling Different Variable Lengths

Character variables with different lengths may also trigger discrepancies in the comparison.

Solution: Ensure that corresponding variables have the same length in both datasets using LENGTH statements.

Conclusion

By addressing issues related to numeric precision, rounding, formatting, and character data, you can reduce or eliminate discrepancies reported by PROC COMPARE in SAS. These solutions ensure more accurate and meaningful comparisons between datasets.

Feel free to leave a comment if you have additional tips or if you’ve encountered other challenges with PROC COMPARE in SAS!

5 Approaches to Identify Blank Columns in SAS Datasets

Author: Sarath

Date: September 5, 2024

When working with large datasets in SAS, it’s crucial to ensure the integrity of your data. One common task is identifying columns that are completely blank, meaning they contain no non-missing values. In this article, we'll explore five different methods to efficiently find blank columns in SAS datasets, along with examples for each approach.

1. Using PROC MEANS or PROC SUMMARY

The simplest way to identify blank columns is by using PROC MEANS or PROC SUMMARY. These procedures provide a summary of missing values for both numeric and character variables. You can quickly determine which columns are fully blank by checking the output for variables with 100% missing values.

proc means data=your_dataset nmiss;
    var _numeric_;  /* For numeric variables */
run;

proc means data=your_dataset nmiss;
    var _character_;  /* For character variables */
run;
    

This method is effective for both numeric and character variables and gives a concise summary of missing values.

2. Using PROC FREQ

Another method is using PROC FREQ to check the frequency of missing values. This approach is suitable when you want to visually inspect the distribution of missing and non-missing values for each column.

proc freq data=your_dataset;
    tables _all_ / missing;
run;
    

In the output, look for variables with 100% missing values, which indicates that the column is blank.

3. DATA STEP Approach with Array

This approach involves using a DATA STEP with arrays to iterate over variables and check for missing values. It's a bit more hands-on and allows for customization, such as flagging rows based on certain conditions.

data check_blank;
    set your_dataset;
    array num_vars _numeric_;  /* For numeric variables */
    array char_vars _character_;  /* For character variables */
    all_blank = 1;  /* Initialize flag */

    do over num_vars;
        if num_vars ^= . then all_blank = 0;
    end;
    
    do over char_vars;
        if char_vars ^= '' then all_blank = 0;
    end;

    if all_blank = 1 then output;
run;
    

This method is useful if you want to customize the logic based on specific variables or values.

4. Using PROC SQL

With PROC SQL, you can query the metadata to identify columns that are fully missing. This approach gives you control over how you want to process the variables and provides a more SQL-like syntax for database programmers.

proc sql;
    select name
    from dictionary.columns
    where libname='WORK' and memname='YOUR_DATASET'
      and missing(name);
quit;
    

This is a quick and efficient way to find completely blank columns using SQL queries.

5. Using a Macro for Automation

If you need to automate the process of checking blank columns across multiple datasets or variables, writing a macro is the best solution. This macro checks each variable for missing values and flags them accordingly.

%macro find_blank_columns(dataset);
    proc contents data=&dataset out=vars(keep=name type) noprint; run;

    data _null_;
        set vars;
        call symputx('var'||left(_n_), name);
        call symputx('type'||left(_n_), type);
        call symputx('nvars', _n_);
    run;

    data check_blank;
        set &dataset;
        %do i = 1 %to &nvars;
            %if &&type&i = 1 %then %do;
                if &&var&i ne . then blank_flag = 0;
            %end;
            %else %do;
                if &&var&i ne '' then blank_flag = 0;
            %end;
        %end;
    run;
%mend find_blank_columns;

%find_blank_columns(your_dataset);
    

This macro can easily be modified to suit different datasets and reporting requirements, providing flexibility for more advanced users.

Conclusion

In this article, we've covered five different approaches to identifying completely blank columns in SAS datasets. Whether you prefer PROC MEANS, PROC FREQ, PROC SQL, or a customized DATA STEP solution, there’s a method here to suit your needs. For users working with large and complex datasets, automating this process using a macro can save time and ensure data integrity.

Explore these techniques and adapt them to your specific use case to streamline your data validation process in SAS.

Happy coding!

Effortlessly Upcase All Variables in SAS Using PROC DATASETS

When working with SAS datasets, ensuring consistency across variables, especially character variables, can be crucial. A common requirement is to upcase all character variables, converting their values to uppercase. While several methods exist to achieve this, one of the most efficient and dynamic approaches involves using the PROC DATASETS procedure. In this article, we will delve into how PROC DATASETS works and how you can use it to upcase all character variables in your dataset with minimal effort.

Understanding PROC DATASETS

The PROC DATASETS procedure is primarily used for managing SAS datasets within a library. It allows you to rename, delete, append, modify, and more, without the need to read or copy the data into the Program Data Vector (PDV). This makes it highly efficient, especially when you need to modify dataset attributes without touching the data itself.

For our specific task of upcasing variables, PROC DATASETS is useful because it allows us to apply a format to all character variables at once, without having to manually iterate over each variable.

Step-by-Step: How to Upcase All Character Variables

1. Identify the Dataset

The first step is to identify the dataset that you want to modify. This dataset should already exist in your specified library. For this example, let’s assume our dataset is original_dataset located in the WORK library.

2. Use the MODIFY Statement in PROC DATASETS

To modify a dataset without reading its data, you can use the MODIFY statement inside PROC DATASETS. This step will tell SAS which dataset you want to apply changes to.

3. Apply an Uppercase Format to All Character Variables

Now, the magic of PROC DATASETS lies in its ability to apply formats to variable types globally. By using the FORMAT statement with _character_, you can apply the $upcase. format to every character variable in the dataset.

Complete Code Example

Here is the full SAS code that applies $upcase. format to all character variables:

proc datasets lib=work nolist;
    modify original_dataset;
    format _character_ $upcase.;
run;
quit;
    

Explanation of the Code

• lib=work: Specifies the library where the dataset is located (in this case, the WORK library).
• nolist: Suppresses the listing of datasets being modified, keeping the log cleaner.
• modify original_dataset: Indicates that we want to modify the dataset named original_dataset.
• format _character_ $upcase.: Applies the $upcase. format to all character variables. This automatically converts the contents of these variables to uppercase.
• run; and quit;: These statements execute the procedure and exit PROC DATASETS.

Advantages of Using PROC DATASETS for Upcasing

There are several advantages to using PROC DATASETS for upcasing character variables:

• Efficiency: PROC DATASETS modifies the dataset in place without reading the data, making it faster and more efficient, especially for large datasets.
• Dynamic Application: By using the _character_ keyword, you don’t need to list out each variable manually. It dynamically selects all character variables and applies the upcase format.
• Minimal Code: Compared to other methods like loops or arrays, PROC DATASETS requires very little code to achieve the same result.
• Works on Multiple Datasets: You can easily modify the code to loop through multiple datasets if needed, by adding more modify statements or using a macro.

Conclusion

Upcasing all character variables in a SAS dataset can be achieved in many ways, but PROC DATASETS offers a streamlined, efficient, and elegant solution. Whether you're dealing with large datasets or want to avoid manually specifying each variable, this method will save you time and effort. Next time you need to perform this task, give PROC DATASETS a try and enjoy its simplicity.

If you have any questions or would like further clarification on using PROC DATASETS in SAS, feel free to leave a comment below!

Mastering SAS: 6 Ways to Upcase All Variables in Your Dataset

How to Upcase All Variables in a SAS Dataset

When working with character data in SAS, you may often need to ensure that all text variables are in uppercase. Fortunately, SAS provides several methods to upcase variables in a dataset, depending on your specific needs. In this article, we will explore various ways to achieve this task and provide code snippets for each method.

1. Using DATA Step with UPCASE Function

The UPCASE function is the simplest way to convert all character variables to uppercase using a DATA step.

data upcase_dataset;
    set original_dataset;
    array char_vars _character_; 
    do i = 1 to dim(char_vars);
        char_vars[i] = upcase(char_vars[i]);
    end;
    drop i;
run;
    

This approach loops through all character variables and applies the UPCASE function to each of them.

2. Using PROC SQL with CASE Statement

In this method, PROC SQL is used with an explicit UPCASE call for each character variable. While this approach is less dynamic, it works well for small datasets.

proc sql;
    create table upcase_dataset as
    select upcase(var1) as var1,
           upcase(var2) as var2,
           upcase(var3) as var3
    from original_dataset;
quit;
    

3. Using PROC DATASETS and FORMAT

With PROC DATASETS, you can apply an uppercase format to all character variables in the dataset in one go.

proc datasets lib=work nolist;
    modify original_dataset;
    format _character_ $upcase.;
run;
quit;
    

4. Using a Macro with PROC SQL

This dynamic method uses a macro to automatically identify and upcase all character variables in the dataset. This is useful for larger datasets or when the number of character variables is unknown.

%macro upcase_all_vars(ds);
    proc sql noprint;
        select name into :char_vars separated by ' '
        from dictionary.columns
        where libname='WORK' and memname=upcase("&ds") and type='char';
    quit;

    data upcase_dataset;
        set &ds;
        %let count = %sysfunc(countw(&char_vars));
        %do i = 1 %to &count;
            %let var = %scan(&char_vars, &i);
            &var = upcase(&var);
        %end;
    run;
%mend;

%upcase_all_vars(original_dataset);
    

5. Using PROC FCMP

This approach involves creating a custom function using PROC FCMP and then applying it across all character variables.

proc fcmp outlib=work.functions.dataset_utils;
    function upcase_all(var $) $;
        return (upcase(var));
    endsub;
run;

options cmplib=work.functions;

data upcase_dataset;
    set original_dataset;
    array char_vars _character_;
    do i = 1 to dim(char_vars);
        char_vars[i] = upcase_all(char_vars[i]);
    end;
    drop i;
run;
    

6. Using HASH Object

An advanced approach is to use the SAS HASH object, which efficiently handles the upcase operation, especially when dealing with large datasets.

data upcase_dataset;
    if _n_ = 1 then do;
        declare hash h(dataset: 'original_dataset');
        h.defineKey('_N_');
        h.defineData('_ALL_');
        h.defineDone();
    end;

    set original_dataset;
    array char_vars _character_;
    do i = 1 to dim(char_vars);
        char_vars[i] = upcase(char_vars[i]);
    end;
    drop i;
run;
    

Conclusion

Each of the methods discussed above has its own strengths, depending on the size of the dataset and whether the variables are known in advance. Whether you're working with small or large datasets, SAS offers a variety of ways to upcase all variables. By selecting the most appropriate approach, you can simplify your workflow and ensure consistency across your data.

If you have any questions or need further assistance, feel free to leave a comment below!