如题,该代码摘自“Safely Merging Many Datasets”一文,可以下载原文http://support.sas.com/resources/papers/proceedings11/409-2011.pdf
/********************************************************
A macro to safely merge all datasets in a libraryby
renaming and relabeling variables that sharenames.
********************************************************/
/* Open the macro */
%macro merge_library;
/* Assign the libref */
libname tomerge 'path/to/datasets';
/* Assign the merge by variable to mergeby macro variable */
%let mergeby=var_name;
/* Get all memnames as macro variables */
proc sql noprint;
select distinct memname,count(distinct memname)
into:ds1 - :ds999,
:dsnum
from dictionary.columns
where libname='TOMERGE' and memname~='MERGED_DATA';
quit;
/* DO loop for each dataset */
%do i=1 %to &dsnum;
/* Sort by mergeby variable */
proc sort data=tomerge.&&ds&i;
by &mergeby;
run;
/* Select duplicate variable names in the current dataset */
proc sql noprint;
select
trim(name)||'='||left(trim(name))||"_&i",
trim(name)||'='||"'"||left(trim("&&ds&i"))||":"
||left(trim(compress(label,"'")))||"'"
into :rename1 - :rename99999,
:relabel1 - :relabel99999
from
(select distinct name, memname, label
from dictionary.columnswhere
libname=upcase("tomerge") and memname=upcase("&&dS&i")and not
indexw(upcase("&mergeby"),upcase(name))and upcase(name) in
(select upcase(name)from dictionary.columnswhere
libname=upcase("tomerge") and memname ne upcase("&&ds&i")));
quit;
/* Store number of duplicate variables as a macro variable */
%let renamenum = &sqlobs;
/* Make a copy and execute rename/label statements */
data copy_&i;
set tomerge.&&ds&i;
%if &renamenum > 0 %then %do;
%do q=1 %to &renamenum;
rename &&rename&q;
label &&relabel&q;
%end;
%end;
run;
/* Special case for the first merge */
%if &i~=1 and &i=2 %then %do;
data tomerge.merged_data;
retain &mergeby;
merge copy_1 copy_&i;
by &mergeby;
run;
%end;
/* Regular case for all subsequent merges */
%else %if &i~=1 %then %do;
data tomerge.merged_data;
retain &mergeby;
merge tomerge.merged_data copy_&i;
by &mergeby;
run;
%end;
%put &i &&ds&i;
/* End DO loop */
%end;
/* Close the Macro */
%mend merge_library;
/* Call the macro */
%merge_library;