This macro first queries the columns dictionary to identify character variables specified in the parameters. It then dynamically generates an SQL procedure to calculate the `max(length())` function for these variables. Finally, it stores these maximum lengths in global macro variables prefixed by 'max' (e.g., &maxVariableName), which is useful for resizing columns or optimizing storage.
Data Analysis
Type : SASHELP
By default, the macro points to SASHELP.CLASS, but it is designed to accept any library and table via the `libin` and `datain` parameters.
1 Code Block
PROC SQL
Explanation :
Retrieval of metadata: selects the names of 'char' type variables present in the target table and corresponding to the list provided in the `vars` parameter.
Retrieval of metadata: selects the names of 'char' type variables present in the target table and corresponding to the list provided in the `vars` parameter.
Copied!
| 1 | PROC SQL noprint; |
| 2 | select name into :charvar1 - :charvar9999 |
| 3 | from dictionary.columns |
| 4 | where LIBNAME=upcase("&LIBIN") |
| 5 | and memname=upcase("&DATAIN") |
| 6 | and type='char' |
| 7 | and findw(UPPER("&vars."), STRIP(UPPER(name))) ne 0 |
| 8 | ; |
| 9 | QUIT; |
| 10 | %let numvars=&SQLOBS; |
2 Code Block
PROC SQL Data
Explanation :
Dynamic generation and execution of an SQL query calculating the maximum length (`max(length(...))`) for each identified variable. The results are stored in a temporary `maxlengths` table.
Dynamic generation and execution of an SQL query calculating the maximum length (`max(length(...))`) for each identified variable. The results are stored in a temporary `maxlengths` table.
Copied!
| 1 | PROC SQL noprint; |
| 2 | create TABLE maxlengths as |
| 3 | select |
| 4 | max(LENGTH(&CHARVAR1)) as max&CHARVAR1 |
| 5 | %IF &NUMVARS > 1 %THEN %DO; |
| 6 | %DO i=2 %to &NUMVARS; |
| 7 | , max(LENGTH(&&CHARVAR&i)) as max&&CHARVAR&i |
| 8 | %END; |
| 9 | %END; |
| 10 | from &LIBIN..&DATAIN |
| 11 | ; |
| 12 | QUIT; |
3 Code Block
DATA STEP
Explanation :
Reading the `maxlengths` table and creating global macro variables (via `CALL SYMPUT`) containing the calculated values, making this information available for the rest of the SAS program.
Reading the `maxlengths` table and creating global macro variables (via `CALL SYMPUT`) containing the calculated values, making this information available for the rest of the SAS program.
Copied!
| 1 | DATA _NULL_; |
| 2 | SET maxlengths; |
| 3 | %DO i=1 %to &NUMVARS; |
| 4 | %global max&&CHARVAR&i; |
| 5 | call symput("max&&CHARVAR&i",max&&CHARVAR&i); |
| 6 | %END; |
| 7 | RUN; |
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
Copyright Info : Copyright 2016 Nada Wasi, Ann Rodgers, Kristin McCue. Distributed under GNU General Public License.
