Macros for SAS Application Developers
https://github.com/sasjs/core
mp_getmaxvarlengths.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Scans a dataset to find the max length of the variable values
4 @details
5 This macro will scan a base dataset and produce an output dataset with two
6 columns:
7
8 - NAME Name of the base dataset column
9 - MAXLEN Maximum length of the data contained therein.
10
11 Character fields are often allocated very large widths (eg 32000) of which the
12 maximum value is likely to be much narrower. Identifying such cases can be
13 helpful in the following scenarios:
14
15 @li Enabling a HTML table to be appropriately sized (`num2char=YES`)
16 @li Reducing the size of a dataset to save on storage (mp_ds2squeeze.sas)
17 @li Identifying columns containing nothing but missing values (`MAXLEN=0` in
18 the output table)
19
20 If the entire column is made up of (non-special) missing values then a value
21 of 0 is returned.
22
23 Usage:
24
25 %mp_getmaxvarlengths(sashelp.class,outds=work.myds)
26
27 @param [in] libds Two part dataset (or view) reference.
28 @param [in] num2char= (NO) When set to NO, numeric fields are sized according
29 to the number of bytes used (or set to zero in the case of non-special
30 missings). When YES, the numeric field is converted to character (using the
31 format, if available), and that is sized instead, using `lengthn()`.
32 @param [out] outds= The output dataset to create, eg:
33 |NAME:$8.|MAXLEN:best.|
34 |---|---|
35 |`Name `|`7 `|
36 |`Sex `|`1 `|
37 |`Age `|`3 `|
38 |`Height `|`8 `|
39 |`Weight `|`3 `|
40
41 <h4> SAS Macros </h4>
42 @li mcf_length.sas
43 @li mf_getuniquename.sas
44 @li mf_getvarlist.sas
45 @li mf_getvartype.sas
46 @li mf_getvarformat.sas
47
48 @version 9.2
49 @author Allan Bowe
50
51 <h4> Related Macros </h4>
52 @li mp_ds2squeeze.sas
53 @li mp_getmaxvarlengths.test.sas
54
55**/
56
57%macro mp_getmaxvarlengths(
58 libds
59 ,num2char=NO
60 ,outds=work.mp_getmaxvarlengths
61)/*/STORE SOURCE*/;
62
63%local vars prefix x var fmt;
64%let vars=%mf_getvarlist(libds=&libds);
65%let prefix=%substr(%mf_getuniquename(),1,25);
66%let num2char=%upcase(&num2char);
67
68%if &num2char=NO %then %do;
69 /* compile length function for numeric fields */
70 %mcf_length(wrap=YES, insert_cmplib=YES)
71%end;
72
73proc sql;
74create table &outds (rename=(
75 %do x=1 %to %sysfunc(countw(&vars,%str( )));
76 &prefix.&x=%scan(&vars,&x)
77 %end;
78 ))
79 as select
80 %do x=1 %to %sysfunc(countw(&vars,%str( )));
81 %let var=%scan(&vars,&x);
82 %if &x>1 %then ,;
83 %if %mf_getvartype(&libds,&var)=C %then %do;
84 max(lengthn(&var)) as &prefix.&x
85 %end;
86 %else %if &num2char=YES %then %do;
87 %let fmt=%mf_getvarformat(&libds,&var);
88 %put fmt=&fmt;
89 %if %str(&fmt)=%str() %then %do;
90 max(lengthn(cats(&var))) as &prefix.&x
91 %end;
92 %else %do;
93 max(lengthn(put(&var,&fmt))) as &prefix.&x
94 %end;
95 %end;
96 %else %do;
97 max(mcf_length(&var)) as &prefix.&x
98 %end;
99 %end;
100 from &libds;
101
102 proc transpose data=&outds
103 out=&outds(rename=(_name_=NAME COL1=MAXLEN));
104 run;
105
106%mend mp_getmaxvarlengths;