Sorry @SASPhile, but I cannot understand the logic of the processing you requested..
Yours in confusion,
Andrew.
Here is an approach that works regardless of the type of variable
/* Sample data sets */
data A;
Var1_A=1;Var2_A=1;output;
Var1_A=1;Var2_A=.;output;
run;
data B;
Var1_B="a";Var2_B="a";Var3_B="a";output;
Var1_B="a";Var2_B="a";Var3_B="";output;
run;
data C;
Var3_C=1;output;
Var4_C=.;output;
run;
/* Source data set */
data Source;
input Dataset $ Variable_List $;
datalines;
A Var1_A
A Var2_A
B Var1_B
B Var2_B
B Var3_B
C Var3_C
C Var4_C
;
data want;
length Dataset $20 Variable_miss $20;
if 0 then set A B C;
set Source(rename=(Variable_List=Variable_miss));
declare hash h(dataset:dataset, multidata:"Y", ordered:"A");
h.definekey(Variable_miss);
h.definedone();
declare hiter hi("h");
hi.first();
if strip(vvaluex(Variable_miss)) in ("",".") then output;
keep dataset Variable_miss;
run;
@SASPhile, I'm curious if this met your needs? Regards.
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;
There are more updates on the enahanement are coming.I havent tested it yet, will let you know if it worked. Thanks for your followup.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.