It often happens when handling complex data in SAS^© that you run into a dynamic referencing problem. Imagine a dataset where one column does not contain raw data, but the name of another variable in the table from which you want to extract the value.

The Scenario

Let's take the example of a dataset containing statistics (FIELD_AVG, FIELD_SUM) and a control column named REQ_STAT.

This REQ_STAT column indicates which statistic should be retained for the current row.

If REQ_STAT is "FIELD_AVG", we want the numeric value from the FIELD_AVG column.
If REQ_STAT is "FIELD_SUM", we want the value from FIELD_SUM.

The goal is to create a new variable, FIELD_STAT, which contains this final value.

The False Good Idea: SYMPUT and SYMGET

The first reflex of many programmers is to try to use macro-variable features at run time via CALL SYMPUT and SYMGET.

The code often looks like this:

1	DATA target_ds;
2	SET source_ds;
3	/* Tentative d'assigner le nom de la variable à une macro */
4	CALL SYMPUT('field_val', req_stat);
5	/* Tentative de récupérer la valeur */
6	field_stat = SYMGET('field_val');
7	RUN;

Why doesn't this work? The result of this code will not be the numeric value (e.g., 120), but the character string itself (e.g., "FIELD_AVG"). The SYMGET function retrieves the content of the macro-variable as is (text) and does not interpret it as a reference to a dataset variable. Moreover, managing macro-variables during the execution of a DATA step is complex because they do not update line by line as one might hope for this type of operation.

The Recommended Solution

To solve this problem of "resolving a character string into a variable name", you must abandon the idea of dynamic resolution by macro during the DATA step.

Two main approaches exist:

1. For a small number of variables: The explicit condition If you only have a few possible columns (as in our example), the simplest and most efficient method remains the use of conditional blocks (IF/THEN/ELSE or SELECT).

1	DATA target_ds;
2	SET source_ds;
3
4	SELECT (req_stat);
5	WHEN ('FIELD_AVG') field_stat = field_avg;
6	WHEN ('FIELD_SUM') field_stat = field_sum;
7	OTHERWISE field_stat = .;
8	END;
9	RUN;

2. For a large number of variables: Code generation If you are dealing with hundreds of columns, writing conditions manually becomes tedious. The solution is then to generate the SAS^© code before execution.

You can use the table's metadata to dynamically build a SELECT statement or a series of IFs via a preprocessing macro. This makes the code generic and adaptable regardless of the table structure.

In SAS^©, a character string containing a variable name remains text. To access the content of this variable, it is better to use explicit logical structures or go through arrays or dynamically generated code, rather than relying on SYMGET which is not designed for strict variable indirection.

Avertissement important

Les codes et exemples fournis sur WeAreCAS.eu sont à but pédagogique. Il est impératif de ne pas les copier-coller aveuglément sur vos environnements de production. La meilleure approche consiste à comprendre la logique avant de l'appliquer. Nous vous recommandons vivement de tester ces scripts dans un environnement de test (Sandbox/Dev). WeAreCAS décline toute responsabilité quant aux éventuels impacts ou pertes de données sur vos systèmes.

Retour à la liste des articles

SAS et tous les autres noms de produits ou de services de SAS Institute Inc. sont des marques déposées ou des marques de commerce de SAS Institute Inc. aux États-Unis et dans d'autres pays. ® indique un enregistrement aux États-Unis. WeAreCAS est un site communautaire indépendant et n'est pas affilié à SAS Institute Inc.