detect % of observations missing using BY

[podarum]

Sorry , I think I was getting 0's because I had none missing.. for a second I was thinking I was only seeing the perecentage of missing... All clear now.. thanks

[FriedEgg]

data foo;

input (store product) ($) v1-v4;

cards;

A A1 5 . 6 3

A A1 7 6 0 9

A A1 6 . 4 .

A A2 . 5 3 3

A A2 5 4 7 5

B B1 3 . 0 8

B B1 . 5 6 7

B B2 . 5 8 9

;

run;

data bar;

  set foo;

  by store product;

  if first.product then do;

   cnt=0;

   nmiss=0;

  end;

  cnt+4;

  nmiss+(coalesce(v1,0)=0) +

        (coalesce(v2,0)=0) +

        (coalesce(v3,0)=0) +

        (coalesce(v4,0)=0);

  if last.product then do;

   pctmiss=nmiss/cnt;

   output;

  end;

  keep store product pctmiss;

run;

proc sql;

create table _bar as

select store,

        product,

        ( sum( (coalesce(v1,0)=0) +

               (coalesce(v2,0)=0) +

               (coalesce(v3,0)=0) +

                           (coalesce(v4,0)=0) ) ) /

        ( count(*)*4 ) as pctmiss

   from foo

  group by store,product;

quit;

[podarum]

Thanks FriedEgg, very interesting approach.. And I had no idea the usage of coalesce, its so usefull.