We’re smarter together. Learn from this collection of community knowledge and add your expertise.

Way to count the non missing values and the Zero count in the Dataset along with the variable.

by Frequent Learner Anind_Sharma on ‎06-10-2014 02:33 AM - edited on ‎10-05-2015 03:49 PM by Community Manager (303 Views)

 

Hi All,

 

I have created this code to count zeros and non missing count for all the variable present in the datset.

 

Hope it can be usefull for you .

 

"

%macro detail(anind1,anind2);

 

libname Ani "c:/desktop";

 

proc contents noprint data =#dataset name # order=ignorecase out=summ_bin; quit;

 

proc sql noprint;

select count (name) into: dscnt

from summ_bin;

%let dscnt=%trim(&dscnt);

select name into: var1 -: var&dscnt

from summ_bin;

quit;

 

%put &dscnt;

/Missing Rate/

%macro fac;

data xyz;

     set &anind2..&anind1 (dataset name);

     %do i = 1 %to &dscnt.;

          %if      "&&var&i."n = .  %then  "&&var&i."n = "";

          %end;

run;

 

proc sql;

create table xxx

as select %do i=1 %to &dscnt.; count("&&var&i"n) as "&&var&i"n, %end;

count(*) as TOTAL from xyz;

quit;

%mend;

%fac;

 

proc transpose data=xxx out=xxx_temp;run;

 

proc sql;

     create table finaloutput

     as select      a._NAME_ as factor, a.COL1 as non_miss, 

                    b.TOTAL as TOTAL,

                    non_miss / total as non_miss_rate

     from xxx_temp as a, xxx as b

     order by non_miss_rate, factor;

quit;

 

proc sql noprint; select non_miss_rate into:y separated by ',' from finaloutput; quit;

%put &y;

 

proc sort data = finaloutput;      by factor;run;

proc sort data = summ_bin (rename=(name=factor));      by factor;run;

 

data check;

merge       finaloutput(in=a) summ_bin(keep=factor NOBS in=b);

by      factor;

if a or b;

 

if      FACTOR not in ("TOTAL" "OBS");

rename      NOBS = bin ;

keep  factor  non_miss_rate TOTAL non_miss;

run;

 

 

/********for Zero's count in each column*********/

 

proc contents data=&anind2..&anind1.(#dataset Name#) out=_xyz(keep=name type varnum) noprint;

run;

 

proc sort data=_xyz;

     by varnum;

run;

 

data null;

     set _xyz nobs=a;

     if N = 1 then call symput('a',a);

     call symput('name'||compress(_N_),name);

     call symput('type'||compress(_N_),type);

run;

 

options symbolgen;

 

%macro test2;

data outfin2;

set &anind2..&anind1. end=last;

%do i=1 %to &a;

/&&name&i_cnt=0;/

if &&type&i=2 then do;

     if "&&name&i"n='0' then %sysfunc(compress(&&name&i.._cnt))+1;

end;

 

else if &&type&i=1 then do;

       if "&&name&i"n=0 then %sysfunc(compress(&&name&i.._cnt))+1;

end;

Keep %sysfunc(compress(&&name&i.._cnt));

%end;

if last;

run;

 

%do i=1 %to &a;

data outfin2;

set outfin2(rename=(%sysfunc(compress(&&name&i.._cnt))=&&name&i.));

run;

%end;

 

%mend test2;

%test2

 

proc transpose data=outfin2 out=outfin21(rename=(_NAME_= Factor COL1=Zero_Count)); run;

 

proc sort data=outfin21;

by factor;

run;

 

/the final out will give you Var Name Along with non miss count and zero count/

 

data finale;

merge outfin21(in=a) check(in=b);

by factor;

if a and b;

run;

 

%mend detail;

"

 

Thanks

Your turn
Sign In!

Want to write an article? Sign in with your profile.