When manipulating data in SAS©, it is often necessary to reorganize the structure of a table. A classic case is converting a dataset where categories are listed in rows ("long" format) to a format where these categories become distinct columns ("large" or "wide" format).
This article explores how to perform this transformation, calculate sums robustly, and apply complex sorting logic.
Let's imagine a source table named Population1 containing demographic data by nation and gender:
The objective is to obtain a table Population2 structured as follows, with a column for the total and separate columns for each gender:
Although it is technically possible to use a complex DATA step with RETAIN statements and conditions, the most efficient and least error-prone method relies on the TRANSPOSE procedure.
Before transposing, it is imperative to sort the data by the grouping variable (here, Nation).
| 1 | /* 1. Tri préalable indispensable */ |
| 2 | PROC SORT DATA = population1; |
| 3 | BY Nation; |
| 4 | RUN; |
| 5 | |
| 6 | /* 2. Transposition */ |
| 7 | PROC TRANSPOSE DATA = population1 out = temp_pop(drop=_name_); |
| 8 | BY Nation; /* La ligne devient unique par Nation */ |
| 9 | id Gender; /* Les valeurs de Gender deviennent les noms de colonnes */ |
| 10 | var Number; /* Ce sont ces chiffres qui remplissent les colonnes */ |
| 11 | RUN; |
Once the columns are created, we need to calculate the total population. There is a crucial difference between simple arithmetic addition and the sum function in SAS©.
| 1 | Population = Men + Women; |
The recommended approach
| 1 | Population = sum(Men, Women); |
Now let's assume a specific sorting constraint:
Sort the nations by descending Population.
Exception: A specific nation (e.g., "MyCountry") must always appear in the last position, regardless of its population figure.
To achieve this without modifying the displayed data, the trick is to create a temporary sorting variable (or "rank").
We assign the population value to this sorting variable for all nations, except for the special one, to which we assign an artificially low value (for example, -1).
| 1 | DATA population2; |
| 2 | SET temp_pop; |
| 3 | |
| 4 | /* Calcul de la somme robuste */ |
| 5 | Population = sum(Men, Women); |
| 6 | |
| 7 | /* Création de la variable de tri temporaire */ |
| 8 | IF Nation = "MyCountry" THEN SortKey = -1; |
| 9 | ELSE SortKey = Population; |
| 10 | RUN; |
| 11 | |
| 12 | /* Tri final sur la clé temporaire */ |
| 13 | PROC SORT DATA = population2; |
| 14 | BY descending SortKey; |
| 15 | RUN; |
| 16 | |
| 17 | /* Nettoyage final : on retire la clé de tri */ |
| 18 | DATA population2; |
| 19 | SET population2(drop=SortKey); |
| 20 | RUN; |
To efficiently transform a table:
Use PROC SORT then PROC TRANSPOSE with an ID statement to pivot rows into columns.
Prefer the SUM() function over the + operator to avoid the propagation of missing values.
For complex sorts (like forcing a row to the bottom of the table), create a temporary technical variable, sort by it, and then drop it.
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.