/* T007670 Optimize the lengths of SAS variables
Character code from Roland Rashleigh-Berry (RIP)
Numeric code from Rick Langston
HAVE
====
Up to 40 obs from class total obs=19
Variables in Creation Order
# Variable Type Len
1 NAME Char 100
2 SEX Char 400
3 AGE Num 8
4 HEIGHT Num 8
5 WEIGHT Num 8
Obs NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69 113
2 Alice F 13 57 84
3 Barbara F 13 65 98
4 Carol F 14 63 103
5 Henry M 14 64 103
6 James M 12 57 83
7 Jane F 12 60 85
8 Janet F 15 63 113
9 Jeffrey M 13 63 84
WANT
====
Variables in Creation Order
# Variable Type Len
1 NAME Char 7
2 SEX Char 1
3 AGE Num 3
4 HEIGHT Num 3
5 WEIGHT Num 3
WORKING CODE
============
* output dataset can be the same as input dataset;
%utl_optlen(inp=class,out=class_cmp);
* make some data;
data class;
length name $100 sex $400;
set sashelp.class;
weight=round(weight);
height=round(height);
run;quit;
FULL SOLUTION
============
/* set the length of all char variables in a sas dataset to longest observed value */
%macro utl_optlen(
inp= /* input dataset */
,out= /* output dataset */
,compress=no /* output compression */
)
/ des="Create a length and retain statement to optimize dataset and variable attributes";
%local retain num char dsid res nvars rc;
* Input exist and/or empty;
%let dsid = %sysfunc(open(&inp,is));
%let nvars = 0;
%if &dsid ne 0 %then %do;
%let nvars = %sysfunc(attrn(&dsid,NVARS));
%end;
%if &dsid ne 0 %then %do; %let rc=%sysfunc(close(&dsid)); %end;
* Chang Chung;
%put %sysfunc(ifc(%sysevalf(%superq(inp )=,boolean) ,ERROR: Please Provide an Input dataset ,));
%put %sysfunc(ifc(%sysevalf(%superq(out )=,boolean) ,ERROR: Please Provide an output dataset ,));
%put %sysfunc(ifc(%sysevalf(%superq(compress )=,boolean) ,ERROR: Please Provide compression ,));
%put %sysfunc(ifc(%sysevalf(%superq(dsid )=0,boolean),ERROR: %sysfunc(sysmsg()) ,));
%put %sysfunc(ifc(%sysevalf(%superq(nvars )=0,boolean),ERROR: Dataset &inp is empty ,));
%let res= %eval
(
%sysfunc(ifc(%sysevalf(%superq(inp )=,boolean),1,0))
+ %sysfunc(ifc(%sysevalf(%superq(out )=,boolean),1,0))
+ %sysfunc(ifc(%sysevalf(%superq(compress )=,boolean),1,0))
+ %sysfunc(ifc(%sysevalf(%superq(nvars )=0,boolean),1,0))
+ %sysfunc(ifc(%sysevalf(%superq(dsid )=0,boolean),1,0))
);
%if &res = 0 %then %do; * passed;
ods listing close;;
ods output position=__layout;
proc contents data=&inp position;
run;quit;
ods listing;
* build retain statement;
* use separated to eliminate leading blanks;
proc sql noprint;
select sum(type='Char'), sum(type='Num')
into :chr separated by ' ', :num separated by ' ' from __layout;
;quit;
data _null_;
set &inp end=dne;
%if &num ne 0 %then %do;
array num[&num] _numeric_ ;
array lennum[&num] _temporary_;
%end;
%if &chr ne 0 %then %do;
array chr[&chr] _character_;
array lenchr[&chr] _temporary_;
do __i=1 to dim(chr);
if lengthn(chr[__i]) > lenchr[__i] then lenchr[__i]=length(chr[__i]);
end;
%end;
* this could be made more elegant but for maintenence reasons I kept it this way;
* Rick Langston first proposed this;
%if &num ne 0 %then %do;
do i=1 to dim(num);
if num[i] ne trunc( num[i], 7 ) then len = 8 ; else
if num[i] ne trunc( num[i], 6 ) then len = 7 ; else
if num[i] ne trunc( num[i], 5 ) then len = 6 ; else
if num[i] ne trunc( num[i], 4 ) then len = 5 ; else
if num[i] ne trunc( num[i], 3 ) then len = 4 ; else len=3;
if len > lennum[i] then lennum[i]=len;
end;
%end;
* build the program to optimize attributes;
if dne then do;
call execute(
"data &out(
compress=&compress
label='Dataset &inp processed by utl_optlen, Variable lengths may have been shortened and compression changed')
;");
call execute( 'retain');
do until (dnepos);
set __layout(keep=variable) end=dnepos;
call execute( variable);
end;
call execute( ';length');
%if &chr ne 0 %then %do;
do __i=1 to dim(chr);
var=catx(' ',vname(chr[__i]),cats('$',put(lenchr[__i],6.)));
call execute( var);
end;
%end;
%if &num ne 0 %then %do;
do __i=1 to dim(num); * do not want I variable;
var=catx(' ',vname(num[__i]),put(lennum[__i],6.));
call execute( var);
end;
%end;
call execute( ";set &inp;run;quit;");
end;
%end; * end do some work;
run;quit;
%mend utl_optlen;
... View more
