BookmarkSubscribeRSS Feed

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

Started ‎06-10-2014 by
Modified ‎10-05-2015 by
Views 1,722


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;



%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 = "";




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;





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;



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;




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


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



proc sort data=_xyz;

     by varnum;



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);



options symbolgen;


%macro test2;

data outfin2;

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

%do i=1 %to &a;


if &&type&i=2 then do;

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



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

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


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


if last;



%do i=1 %to &a;

data outfin2;

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




%mend test2;



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


proc sort data=outfin21;

by factor;



/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;



%mend detail;




Version history
Last update:
‎10-05-2015 03:49 PM
Updated by:


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags