SEEKING AN EFFICIENT WAY OF COUNTING

[novinosrin]

@d6k5d3  Here I am re-posting it

data have;
input (Date      Time     Ask      Bid       Day       Price     Return) ($);
cards;
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Mon     xxx        0
xxx         xxx       xxx       xxx       Mon     xxx        0
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Mon     xxx        0
xxx         xxx       xxx       xxx       Mon     xxx        0
xxx         xxx       xxx       xxx       Mon     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx       xxxx
xxx         xxx       xxx       xxx       Tue     xxx       xxxx
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx       xxxx
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
xxx         xxx       xxx       xxx       Wed     xxx        0
xxx         xxx       xxx       xxx       Wed     xxx        0
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
xxx         xxx       xxx       xxx       Wed     xxx       0
xxx         xxx       xxx       xxx       Wed     xxx       0
xxx         xxx       xxx       xxx       Wed     xxx       0
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
;

data grp;
set have;
by day return notsorted;
if first.day then Incid_of_consec0s =0;
if first.return and return='0' then Incid_of_consec0s+1;
if return='0';
run;

proc sql;
create table want as
select day,Incid_of_consec0s,sum(return='0') as zero_count_in_each_incid
from grp
group by day,Incid_of_consec0s;
quit;

[d6k5d3]

lost words for saying sorry. I missed it because I was writing the replies, and was trying to clarify. that is why I may have missed it. thank you very much.

[novinosrin]

No worries 🙂

[VDD]

sorry that I may have not interpreted @d6k5d3 I would like to know for the same day how many returns are 0s statement.

You should have said something about date in your original request so that the your requested was fully stated.

[novinosrin]

data have;
input (Date      Time     Ask      Bid       Day       Price     Return) ($);
cards;
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
xxx         xxx       xxx       xxx       Wed     xxx        0
xxx         xxx       xxx       xxx       Wed     xxx        0
;

proc sql;
create table want as
select day,sum(return='0') as zero_retun_count
from have
group by day;
quit;

[novinosrin]

data have;
input (Date      Time     Ask      Bid       Day       Price     Return) ($);
cards;
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Mon     xxx        xxxx
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Tue     xxx        0
xxx         xxx       xxx       xxx       Wed     xxx        xxxx
xxx         xxx       xxx       xxx       Wed     xxx        0
xxx         xxx       xxx       xxx       Wed     xxx        0
;
data want;   
     set have;
     by day notsorted;
     if first.day then count=0;
     count +return='0';
     if last.day ;
run;

[Astounding · Accepted solution]

After sifting through many messages, here is what I think you are looking for:

data want;

set have;

by date return notsorted;

if first.date then incident_no = 0;

if first.return then consecutive=1;

else consecutive + 1;

if last.return;

if return='0';

incident_no + 1;

run;

[d6k5d3]

Thank you so very much for understanding me! This is what exactly I wanted! Hats off!