Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_getmaxvarlengths.sas
Go to the documentation of this file.
1 /**
2  @file mp_getmaxvarlengths.sas
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 may be allocated very large widths (eg 32000) of which the maximum
12  value is likely to be much narrower. This macro was designed to enable a HTML
13  table to be appropriately sized however this could be used as part of a data
14  audit to ensure we aren't over-sizing our tables in relation to the data therein.
15 
16  Numeric fields are converted using the relevant format to determine the width.
17  Usage:
18 
19  %mp_getmaxvarlengths(sashelp.class,outds=work.myds)
20 
21  @param libds Two part dataset (or view) reference.
22  @param outds= The output dataset to create
23 
24  <h4> Dependencies </h4>
25  @li mf_getvarlist.sas
26  @li mf_getvartype.sas
27  @li mf_getvarformat.sas
28 
29  @version 9.2
30  @author Allan Bowe
31 
32 **/
33 
34 %macro mp_getmaxvarlengths(
35  libds /* libref.dataset to analyse */
36  ,outds=work.mp_getmaxvarlengths /* name of output dataset to create */
37 )/*/STORE SOURCE*/;
38 
39 %local vars x var fmt;
40 %let vars=%mf_getvarlist(libds=&libds);
41 
42 proc sql;
43 create table &outds (rename=(
44  %do x=1 %to %sysfunc(countw(&vars,%str( )));
45  ________&x=%scan(&vars,&x)
46  %end;
47  ))
48  as select
49  %do x=1 %to %sysfunc(countw(&vars,%str( )));
50  %let var=%scan(&vars,&x);
51  %if &x>1 %then ,;
52  %if %mf_getvartype(&libds,&var)=C %then %do;
53  max(length(&var)) as ________&x
54  %end;
55  %else %do;
56  %let fmt=%mf_getvarformat(&libds,&var);
57  %put fmt=&fmt;
58  %if %str(&fmt)=%str() %then %do;
59  max(length(cats(&var))) as ________&x
60  %end;
61  %else %do;
62  max(length(put(&var,&fmt))) as ________&x
63  %end;
64  %end;
65  %end;
66  from &libds;
67 
68  proc transpose data=&outds
69  out=&outds(rename=(_name_=NAME COL1=MAXLEN));
70  run;
71 
72 %mend;