topic Re: SEEKING AN EFFICIENT WAY OF COUNTING in New SAS User

SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 — Fri, 14 Dec 2018 18:27:15 GMT

I have a dataset that looks like this:

Date Time Ask Bid Day Price Return

xxx xxx xxx xxx Mon xxx xxxx

xxx xxx xxx xxx Tue xxx 0

xxx xxx xxx xxx Wed xxx xxxx

xxx xxx xxx xxx Wed xxx 0

I would like to know for the same day how many returns are 0s.

Much thanks.

Re: SEEKING AN EFFICIENT WAY OF COUNTING

PeterClemmensen — Fri, 14 Dec 2018 18:32:49 GMT

Do something like this

proc sql;
   create table want as
   select date, 
          count(return)
   where return eq 0
   group by date;
quit;

Re: SEEKING AN EFFICIENT WAY OF COUNTING

VDD — Fri, 14 Dec 2018 18:34:56 GMT

data want;
     retain count 0;
     set have;
     by day notsorted;
     if first.day then count=0;
     if return = 0 then count +1;
     if last.day output;
run;

Re: SEEKING AN EFFICIENT WAY OF COUNTING

novinosrin — Fri, 14 Dec 2018 18:38:45 GMT


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;

Re: SEEKING AN EFFICIENT WAY OF COUNTING

novinosrin — Fri, 14 Dec 2018 18:43:55 GMT


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;

Re: SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 — Fri, 14 Dec 2018 18:52:08 GMT

@VDD, @PeterClemmensen,@novinosrin, Please excuse me for not being able to convey what I really wanted. Your codes would give me total counts for the days where return=0. I actually something different. What's bugging me in the dataset is that I see a lot of consecutive 0s. Let's look at the dataset again:

Date Time Ask Bid Day Price Return

xxx xxx xxx xxx Mon xxx xxxx

xxx xxx xxx xxx Mon xxx 0

xxx xxx xxx xxx Mon xxx xxxx

xxx xxx xxx xxx Mon xxx 0

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 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 xxxx

xxx xxx xxx xxx Wed xxx 0

xxx xxx xxx xxx Wed xxx xxxx

I actually would like to know how many incidents of consecutive zeros, and how many zeros in each incident are there for each day. for example, if we look at Wednesday, we see that there are 2 incidents of consecutive zeros: in the 1st one there 2 zeros, and in the 2nd one, there are 3. For Tuesday there's only one incident of consecutive zeros having 3 zeros.

How can I achieve this?

Much thanks. I wholeheartedly appreciate the lessons I receive from you guys!

Re: SEEKING AN EFFICIENT WAY OF COUNTING

novinosrin — Fri, 14 Dec 2018 19:21:05 GMT

Please post a sample of your expected output. That can only help and also helps people not to assume

Re: SEEKING AN EFFICIENT WAY OF COUNTING

VDD — Fri, 14 Dec 2018 19:26:37 GMT

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.

Re: SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 — Fri, 14 Dec 2018 19:44:51 GMT

@novinosrin& @VDD: thank you for your feedback. I would like to see a result like this:

Day Incident_of_consecutive_0s Count_of_Zeros_in_each_incident

Mon 1 4

Mon 2 20

Mon 3 5

Tue 1 2

Tue 2 14

… … ...

Much thanks.

Re: SEEKING AN EFFICIENT WAY OF COUNTING

novinosrin — Fri, 14 Dec 2018 19:47:09 GMT

Sorry I am lost, where is the variable incident in your input sample or if it is derived from the input, what's the rule for that? kindly make sure your sample output matches the input sample and reason it. If not, it becomes very hazy, at least for me

EDIT:I see only two incidence of consecutive zeros for Mon and your output says 3. Also the counts are way apart

Re: SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 — Fri, 14 Dec 2018 19:55:09 GMT

Sorry for all the confusion. The 'incident' variable is not there in the input sample. It's a new one that would be created. In my input dataset, you would see for each day, there are numbers of consecutive zero returns. Something like 2 zero returns consecutively, and then 1 or 2 non-zero returns, and then again some consecutive zero returns.

what I would like to find is how many times the 'incidents' of those consecutive zero returns occur, and how many 0s are there in each incident.

For example, in case of Tuesday there's only one incident of consecutive zeros. So in this case Incident_of_consecutive_0s=1 and Count_of_Zeros_in_each_incident=3.

Re: SEEKING AN EFFICIENT WAY OF COUNTING

VDD — Fri, 14 Dec 2018 19:55:11 GMT

@d6k5d3your request is becoming more confusing as you are adding conduction that were not stated nor supported in your samples.

Please ensure that your request states what you have what you want and includes sample values for the input and output in the form you have the data ratter that X's and 0's.

Re: SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 — Fri, 14 Dec 2018 19:56:34 GMT

Oh sorry. I just showed you the format of the output. The results are not necessarily the actual ones.

Re: SEEKING AN EFFICIENT WAY OF COUNTING

novinosrin — Fri, 14 Dec 2018 20:04:26 GMT

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;

Re: SEEKING AN EFFICIENT WAY OF COUNTING

VDD — Fri, 14 Dec 2018 20:04:49 GMT

When you posted your question at this location did you read the check list and check those off before pressing post?
https://communities.sas.com/t5/forums/postpage/board-id/new-users

Stop right there! Before pressing POST, tick off this checklist. Does your post …
✔ Have a descriptive subject line, i.e., How do I ‘XYZ’?
✔ Use simple language and provide context? Definitely mention what version you’re on.
✔ Include code and example data? Consider using the SAS Syntax feature.

Please help us help you.

Re: SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 — Fri, 14 Dec 2018 20:10:06 GMT

@VDDand @novinosrin, I cannot thank you enough for the time you are taking to look into this. For one last time, I am trying all over again. My dataset is like the one below:

Date Time Ask Bid Day Price Return

xxx xxx xxx xxx Mon xxx xxxx

xxx xxx xxx xxx Mon xxx 0 | this is the 1st 'incident' of consecutive zero for Monday. it has 2 zeros

xxx xxx xxx xxx Mon xxx 0 |

xxx xxx xxx xxx Mon xxx xxxx

xxx xxx xxx xxx Mon xxx 0 |

xxx xxx xxx xxx Mon xxx 0 | this is 2nd 'incident' of consecutive zero for Monday. it has 3 zeros

xxx xxx xxx xxx Mon xxx 0 |

xxx xxx xxx xxx Tue xxx 0 |

xxx xxx xxx xxx Tue xxx 0 | this is 1st and only 'incident' of consecutive zero for Tuesday.

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 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 xxxx

xxx xxx xxx xxx Wed xxx 0

xxx xxx xxx xxx Wed xxx xxxx

Please look at the comments on the right side above. If this is the dataset, I want to know how many incidents of consecutive zeros occur in each day, and how many zeros are there in each 'incident'. so I would need to create a new variable 'incident' which will count the number of incidents of consecutive zeros. I would also need to create another variable number_of_zeros_in_each_incident which will count the consecutive zeros in the each of the incidents. So my output should look like this:

Day Incident_of_consecutive_0s Count_of_Zeros_in_each_incident

Mon 1 2

Mon 2 3

Tue 1 3

Wed 1 2

Wed 2 3

Re: SEEKING AN EFFICIENT WAY OF COUNTING

novinosrin — Fri, 14 Dec 2018 20:14:45 GMT

I posted the code. Please see @d6k5d3 the thread. It's there. Seems like you are not reviewing the thread properly or you are not get my notification

Re: SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 — Fri, 14 Dec 2018 20:13:58 GMT

I am so sorry. I am really not sure how I could fit what I want to do in the subject line. Since it's all about counting, I kept it so. But I am still thinking.

Re: SEEKING AN EFFICIENT WAY OF COUNTING

novinosrin — Fri, 14 Dec 2018 20:16:36 GMT

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

Re: SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 — Fri, 14 Dec 2018 20:32:02 GMT

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.