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;

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

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

sas-innovate-2024.png

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