Solved: Re: SEEKING AN EFFICIENT WAY OF COUNTING

d6k5d3 · Posted 12-14-2018 01:27 PM

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.

Astounding · Posted 12-14-2018 03:52 PM

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;

View solution in original post

PeterClemmensen · Posted 12-14-2018 01:32 PM

Do something like this

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

The DATA to DATA Step Macro
Blog: SASnrd

VDD · Posted 12-14-2018 01:34 PM

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;

d6k5d3 · Posted 12-14-2018 01:52 PM

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

novinosrin · Posted 12-14-2018 02:21 PM

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

d6k5d3 · Posted 12-14-2018 02:41 PM

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

novinosrin · Posted 12-14-2018 02:45 PM

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

d6k5d3 · Posted 12-14-2018 02:55 PM

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.

d6k5d3 · Posted 12-14-2018 02:56 PM

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

novinosrin · Posted 12-14-2018 03:04 PM

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;

VDD · Posted 12-14-2018 03:04 PM

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.

d6k5d3 · Posted 12-14-2018 03:13 PM

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.

VDD · Posted 12-14-2018 02:55 PM

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

d6k5d3 · Posted 12-14-2018 03:10 PM

@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

novinosrin · Posted 12-14-2018 03:13 PM

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

SAS Innovate 2025: Save the Date