Program below would be helpful in consolidating the variables that has got only missing values across the library proc sql noprint;
create table tblscn as select memname, libname from dictionary.tables where libname='TMP';
quit;
%macro loop;
%local num i mv_memname mv_libname;
%let dsid = %sysfunc(open(work.tblscn));
%let num = %sysfunc(attrn(&dsid,nlobs));
%let rc = %sysfunc(close(&dsid));
%do i=1 %to #
data _null_;
p = &i;
set work.tblscn point=p;
call symputx('mv_memname',memname);
call symputx('mv_libname',libname);
stop;
run;
data _null_;
set &mv_libname..&mv_memname. end=last;
array _a{*} $ _character_;
array _b{*} _numeric_;
length varname memname libname $32;
if _n_ = 1 then do;
call missing(var, varn, varname, memname, libname);
declare hash nmc (ordered:"a");
nmc.definekey("var");
nmc.definedata('var','varname','memname','libname');
nmc.definedone();
declare hiter iter('nmc');
do var=1 to dim(_a);
nmc.add(key:var,data:var,data:vname(_a[var]),data:"&memname.",data:"&libname.");
put _all_;
end;
declare hash nmn (ordered:"a");
nmn.definekey("varn");
nmn.definedata('varn','varname','memname','libname');
nmn.definedone();
declare hiter niter('nmn');
do varn=1 to dim(_b);
nmn.add(key:varn,data:varn,data:vname(_b[varn]),data:"&memname.",data:"&libname.");
put _all_;
end;
end;
rc=iter.first();
do while (rc = 0);
if _a[var] ne '' then do;
_x=var;
rc=iter.next();
nmc.remove(key:_x);
end;
else rc=iter.next();
end;
rc=niter.first();
do while (rc = 0);
if _b[varn] ne . then do;
_y=varn;
rc=niter.next();
nmn.remove(key:_y);
end;
else rc=niter.next();
end;
if last then do;
nmc.output(dataset:'mc_ds');
nmn.output(dataset:'mcn_ds');
end;
run;
proc append base=mc_ds data=mcn_ds(rename=varn=var) force; run;
proc append base=all_missing_var data=mc_ds force; run;
%end;
%mend loop;
%loop;
... View more
