Sometimes a SAS© program crashes with an IF statement, but works perfectly if you replace that IF with a WHERE. Is it magic? No, it's an architectural difference.
A forum user encountered a cryptic error: "INVALID THIRD ARGUMENT TO FUNCTION SUBSTR". Let's analyze this classic case to understand SAS©'s internal mechanics.
The user wants to split their data into two tables based on the province. They are using complex logic involving SUBSTR, TRIM, and LEFT.
| 1 | DATA bundrpt regulert; |
| 2 | SET combine; |
| 3 | /* La condition qui plante */ |
| 4 | IF (substr(trim(left(brprov)),1,7) = 'ALBERTA') OR |
| 5 | (substr(trim(left(brprov)),1,12) = 'SASKATCHEWAN' and put(cidkey,sask.) = 'Y') |
| 6 | THEN OUTPUT bundrpt; |
| 7 | ELSE OUTPUT regulert; |
| 8 | RUN; |
The Error:
NOTE: INVALID THIRD ARGUMENT TO FUNCTION SUBSTR AT LINE 302BRPROV=ALBERTA
Why is SAS© complaining about the 3rd argument of SUBSTR (the requested length) when the BRPROV variable is 18 characters long?
The error doesn't come from the original variable, but from what the code does to it before calling SUBSTR.
Let's look at the function chain for the value "ALBERTA":
LEFT(BRPROV): Aligns the text to the left.
TRIM(...): Removes trailing spaces.
Temporary result: The string becomes "ALBERTA" (length = 7).
SUBSTR(..., 1, 12): The code then tries to extract 12 characters to check if it's "SASKATCHEWAN".
The Crash: You are asking SAS© to extract 12 characters from a string that is now only 7 characters long (because of TRIM). This is mathematically impossible, so the Data Step stops.
The user notes: "If I use WHERE instead of IF, I don't get an error message."
This is where the fundamental difference lies:
When: Executes during the processing of each row, in the PDV (Program Data Vector).
Behavior: It manipulates data after transformation. Here, it sees the result of the TRIM (the shortened string) and fails. Furthermore, depending on the logical evaluation rules, SAS© might try to evaluate the second part of the OR even if the first seems sufficient, or simply fail during argument compilation.
When: Executes before the data enters the Data Step.
Behavior: It acts as an SQL filter on the data source.
Why does it work? The WHERE statement is often "smarter" or more "restrictive" about the functions it allows. It will often ignore cosmetic functions like TRIM and rely on the source column's metadata (which is indeed 18 characters long), or optimize the query differently.
Rather than relying on the tolerance of WHERE, the code logic must be corrected.
As a user suggested, the TRIM is unnecessary here. SUBSTR works just fine on a string with spaces, as long as the original variable is long enough.
| 1 | /* Pas de TRIM, donc on travaille sur les 18 caractères d'origine */ |
| 2 | IF substr(brprov, 1, 7) = 'ALBERTA' ... |
=: (The Pro Approach)SAS© has a specific operator to say "Starts with," which avoids having to count characters and use SUBSTR.
It's the =: operator (equals sign followed by a colon).
Optimized Code:
| 1 | DATA bundrpt regulert; |
| 2 | SET combine; |
| 3 | /* Plus de SUBSTR, plus de TRIM, plus d'erreurs de longueur ! */ |
| 4 | IF brprov =: 'ALBERTA' OR |
| 5 | (brprov =: 'SASKATCHEWAN' and put(cidkey,sask.) = 'Y') |
| 6 | THEN OUTPUT bundrpt; |
| 7 | ELSE OUTPUT regulert; |
| 8 | RUN; |
| Statement | Role | Error Handling |
| IF | Programming logic. Executes line by line on values in memory. | Strict. If you create a short string (TRIM) and request a long read (SUBSTR), it crashes. |
| WHERE | Input filter (I/O). Executes on the source file. | More robust because it often works on raw metadata or via the SQL engine. |
Expert's advice: If you need to check the beginning of a character string, forget SUBSTR. Always use the =: operator. It's more readable, faster, and less prone to length errors.
Los códigos y ejemplos proporcionados en WeAreCAS.eu son con fines educativos. Es imperativo no copiarlos y pegarlos ciegamente en sus entornos de producción. El mejor enfoque es comprender la lógica antes de aplicarla. Recomendamos encarecidamente probar estos scripts en un entorno de prueba (Sandbox/Dev). WeAreCAS no acepta ninguna responsabilidad por cualquier impacto o pérdida de datos en sus sistemas.