I profundly agree with what has been said about using Excel as a database, but would like to answer the question without work around. So assuming you rely on both Excel database and SAS import, you could automatically unify the variable names and types to ensure correct setting. proc datasets lib=work kill nolist;run;quit;
** Create test data;
data A1_FINAL;
a=1;
b='a1 ';
c=.;
output;
run;
data A2_FINAL;
a=1;
b='a2 ';
c=.;
output;
run;
data A3_FINAL;
a=1;
b='a3 ';
c=.;
output;
run;
data A4_FINAL;
a=1;
b='a4 ';
c=.;
output;
run;
data A5_FINAL;
a='a5';
b=5;
c=.;
output;
run;
%macro renameWorkDs(ds);
data &ds._mod;
set &ds.;
run;
%mend;
%renameWorkDs(A1_FINAL);
%renameWorkDs(A2_FINAL);
%renameWorkDs(A3_FINAL);
%renameWorkDs(A4_FINAL);
%renameWorkDs(A5_FINAL);
** select tables in which same variables have different lengths or types;
proc sql;
create table all_t1 as
select memname,name,type,case when type eq 'num' then length*2 else length end as length
from sashelp.vcolumn
where libname eq 'WORK' and memname in ('A1_FINAL', 'A2_FINAL', 'A3_FINAL', 'A4_FINAL', 'A5_FINAL')
group by name
having count(distinct type) gt 1 or count(distinct length) gt 1
order by name, length desc , type
;
quit;
** Prepare statement to unify length and type of these variables on all tables;
data all_t2;
length code $200;
set all_t1;
by name descending length type;
retain maxlength;
if first.name then maxlength=length;
if type eq 'num' then do;
code='attrib '||strip(name)||'_tmp length=$'||strip(put(maxlength,best32.))||'; '
||strip(name)||'_tmp=strip(put('||strip(name)||',best32.)); '
||'drop '||strip(name)||';';
end;
else do;
code='attrib '||strip(name)||'_tmp length=$'||strip(put(maxlength,best32.))||'; '
||strip(name)||'_tmp=put('||strip(name)||',$'||strip(put(maxlength,best32.))||'.); '
||'drop '||strip(name)||';';
end;
run;
proc sort data=all_t2;
by memname name;
run;
** Perform unification;
data _NULL_;
set all_t2 end=last;
by memname name;
if first.memname then call execute('data '||strip(memname)||'_mod; set '||strip(memname)||'_mod; ');
call execute(strip(code));
if last then call execute('run;');
run;
DATA a1_a5_final;
SET
a1_final_mod
a2_final_mod
a3_final_mod
a4_final_mod
a5_final_mod;
RUN;
** clean work, rename variables to their original names;
data _NULL_;
set sashelp.vcolumn end=last;
where libname eq 'WORK' and memname eq 'A1_A5_FINAL' and substr(name,length(name)-3) eq '_tmp';
if _N_ eq 1 then call execute('proc datasets lib=work;
delete all_t1 all_t2 a1_final_mod a2_final_mod a3_final_mod a4_final_mod a5_final_mod
;
modify A1_A5_FINAL;
');
call execute(' rename '||strip(name)||'='||substr(name,1,length(name)-4)||';');
if last then call execute('run;quit;');
run;
... View more
