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

[#1]

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