Try this:
%macro reduce_length(dataset);
%let lib=%upcase(%sysfunc(scan(&dataset,1,.)));
%let ds=%upcase(%sysfunc(scan(&dataset,2,.)));
proc sql noprint;
select name into :names separated by ' '
from dictionary.columns
where libname = "&lib" and memname = "&ds" and type = 'char'
;
select length into :lengths separated by ' '
from dictionary.columns
where libname = "&lib" and memname = "&ds" and type = 'char'
;
quit;
%let num_vars=%sysfunc(countw(&names));
data lengths;
set &dataset (keep=&names) end=done;
array vars {*} &names;
array lens {&num_vars} _temporary_
;
do i = 1 to dim(vars);
lens{i} = max(lens{i},length(vars{i}));
end;
if done
then do;
do i = 1 to dim(vars);
__name = vname(vars{i});
__length = lens{i};
output;
end;
end;
keep __name __length;
run;
data _null_;
set lengths end=done;
if _n_ = 1
then do;
call execute("data work.new_&ds.; length ");
end;
call execute(trim(__name) !! " $" !! trim(put(__length,5.)) !! " ");
if done then call execute("; set &dataset.; run;");
run;
%mend;
It retrieves the defined lengths from dictionary.columns, then runs a data step with array processing to get the maximum actual lengths, writes them to a dataset, and then creates a data step that sets the new lengths.
- the order of variables in the resulting dataset will change, as all character variables will move to the left (front).
- you will get warnings about multiple lengths specified.
I tried it with sashelp.cars, and it reduced the length of type from 8 to 6.
You might consider adding additional macro parameters to specify the output library or similar.
... View more