How to Conditionally Extract and Concatenate Variable Names

Let's imagine a dataset containing patient identifiers and several columns corresponding to different studies (Study1ID, Study2ID, etc.). If a patient is enrolled in a study, the column contains an ID. Otherwise, it is empty or missing.

Input Data:

Desired Result:

We want to create a new variable (e.g., Other_enrolled_studies) that lists the names of the studies the patient is participating in, separated by a comma.

The key to solving this problem lies in the VNAME() function. Unlike most SAS^© functions that process the value of a variable, VNAME returns the name of the variable itself.

Combined with a loop over an array (ARRAY), this function allows for dynamic iteration through columns.

Method 1: The Iterative Approach (Recommended)

This method is the most direct and often the most efficient. It consists of:

1. Declaring an ARRAY that groups all the columns of interest.

2. Looping over this array.

3. Checking if the value is not missing.

4. If a value exists, concatenate the variable name (via VNAME) to our result variable.

Key points of the code:

• study:: The colon is a wildcard character that selects all variables starting with the prefix "Study".

• catx(", ", ...): This function is ideal because it automatically handles separators. It does not add a comma at the beginning of the string if it is empty, and it removes unnecessary spaces.

Method 2: The Parallel Arrays Approach

Another way to do this is to use two arrays: one for the numeric values and another to store the names as characters, then concatenate everything at the end.

1
 

Note on Performance

Although the second method works, it can be less performant on very large datasets if the catx function is placed inside the loop. Indeed, concatenating an entire list in each iteration is an unnecessarily costly operation.

If you use the iterative approach (Method 1), using catx line by line as the conditions are met remains very efficient because only one character string is built progressively.