Hi, I made this code some time ago. The macro takes a library in library=parameter and all the datasets inside. Checks for columns with all variables missing and does a copy of the table which is written to library in outlib=parameter. It also adds 'nomiss' string at the end of each table name. As the macro generates a lot of code, it is written to temporary external file %macro new(library=,outlib=); %local i j k; proc sql noprint; select memname into :tables separated by " " from dictionary.tables where libname="%upcase(&library.)"; quit; %do i = 1 %to %util_countwords(&tables.);   proc sql noprint;   create table my_dictionary_columns as   select name, monotonic() as N from dictionary.columns   where memname="%scan(&tables,&i.)" and libname="%upcase(&library.)";   quit; data _null_; file 'temp.txt'; set my_dictionary_columns end=the_end; put "retain miss" _N_" 0; miss" _N_"=missing"; put "(" name ")"; put  "+miss" _N_";keep miss" _N_";"; if the_end then call symputx('N_columns',_N_); run; /*  option symbolgen;*/   data miss%scan(&tables,&i.);   set %upcase(&library.).%scan(&tables,&i.) end=the_end;   %include 'temp.txt';   nasumeno+1;   keep nasumeno;   if the_end then output;   run;   data concated;   set miss%scan(&tables,&i.);   array misses miss: ;   keep i ;   do i = 1 to dim(misses);       if misses/nasumeno=1 then do;     check+1;   output;     end;   end;   call symputx('check',check);   run;   proc sql noprint;   create table to_delete as   select a.name   from my_dictionary_columns as a inner join concated as b on(a.N=b.i)   ;   quit;   data _null_;   file 'drop_temp.txt';   set to_delete;   put "drop " name ";";   run; /****************************************************************************************************/   data &outlib..%substr(%scan(&tables,&i.),1,%sysfunc(min(27,%length(%scan(&tables,&i.)))))nomis;   set %upcase(&library.).%scan(&tables,&i.);   ;   %if &check.>0 %then %do;   %include 'drop_temp.txt';   %put &check. variables removed due to missing values from %upcase(&library.).%scan(&tables,&i.);   %end;   %else %do;   %put no missing data discovered in table %upcase(&library.).%scan(&tables,&i.);   %end;   run; %end; %mend new; %new(outlib=&outlib.,library=&library.); Jakub ... View more

Hi, I would suggest to sort the string before inputing it into the macro. For example you could use array sort function. data _null_;   set sashelp.class; array temp(3) $ _temporary_; str1=strip(put(age,best32.))||',x,'||strip(put(height,best32.)); put str1; do i = 1 to dim(temp);    temp{i}=scan(str1,i,',');    end; call sortc(of temp{*}); do i = 1 to dim(temp); if i=1 then str1=temp{1};   else do;   str1=strip(str1)||','||strip(temp{i});   end; end; put str1;   stop; run; Good luck, Jakub ... View more

Hi, for me it works well. For example in this context data my; str = 'a_b_cd___efg_h__ijkl___m'; a=TRANSLATE(str,' ','_'); b=TRANWRD(str,'_',' '); run; a=a b cd   efg h  ijkl   m; b=a b cd   efg h  ijkl   m; Maybe the problem is elsewhere? Jakub ... View more

I think your macrovariable does not exist after the macro run. Try to move the %put statement inside the macro and see what happens. If you want the macro variable to be accessible after the macro is executed you can either use the %global statement or just create the variables before the macro run. By creation I mean for example %let diag2=; Jakub ... View more

If you want to avoid the warning you can use the following code. It renames the variables causing the trouble first: proc sql; create table TAB as select * from DATASET1 as a inner join DATASET2(rename=(VAR1=NEWVAR1 VAR2=NEWVAR2 VAR3=NEWVAR3 VAR4=NEWVAR4 VAR5=NEWVAR5)) as b on a.VAR1=b.NEWVAR1; ; quit; Jakub ... View more