topic Loop through datasets and variables in source file in New SAS User

Loop through datasets and variables in source file

SASPhile — Mon, 01 Oct 2018 17:50:14 GMT

Source File:

Dataset Variable_List

A Var1_A

A Var2_A

B Var1_B

B Var2_B

B Var3_B

C Var3_c

C Var4_C

Source file has above two columns.Before I start processing the data,I want to check if variables in Dataset A and B have any missing values. If they are present then I want report them to a dataset:

say for example, Var2_A and Var3_B ,Var4_C has a missing values

Data_Report table:

Dataset Variable_miss

A Var2_A

B Var3_B

C Var4_C

Re: Loop through datasets and variables in source file

AndrewHowell — Tue, 02 Oct 2018 09:29:49 GMT

Sorry @SASPhile, but I cannot understand the logic of the processing you requested..

Where are there missing values in the "source file"?
And if you want "Var2_A" because there's a "Var2" in B and/or C, and you want "Var3_B" because there's a Var3 in A and/or C, but why do you want "Var4_C" if there's no "Var4" in A and/or B?

Yours in confusion,

Andrew.

Re: Loop through datasets and variables in source file

PeterClemmensen — Wed, 07 Nov 2018 10:09:47 GMT

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.

Re: Loop through datasets and variables in source file

saivenkat — Tue, 02 Oct 2018 11:52:32 GMT

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 &num;
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;

Re: Loop through datasets and variables in source file

PeterClemmensen — Mon, 07 Oct 2019 09:35:18 GMT

@SASPhile , I'm curious if this code met your needs?

Regards.

Re: Loop through datasets and variables in source file

SASPhile — Thu, 08 Nov 2018 16:12:43 GMT

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.