New Contributor
Posts: 2

# Resetting a Counting Variable

Hi,

I would like assistance on how to get a count function that has two variables that could cause it to reset. I am looking at agents and the number of sales they have made per day. I want to have a count function that counts the number of consecutive zeros each agent has. At the moment I have a table that looks like this:

 Agent Date Number of Sales 1 01-Jan 0 1 02-Jan 1 1 03-Jan 3 1 04-Jan 0 1 05-Jan 0 2 01-Jan 0 2 02-Jan 0 2 03-Jan 0 2 04-Jan 2 3 01-Jan 1 3 02-Jan 0 3 03-Jan 0 3 04-Jan 3 3 05-Jan 4

And I would like to have a count function that looks like this (I really dont mind if the count variable when the ticket isnt zero is one or zero):

 Agent Date Number of Sales Count 1 01-Jan 0 1 1 02-Jan 1 0 (or 1) 1 03-Jan 0 1 1 04-Jan 0 2 1 05-Jan 0 3 2 01-Jan 0 1 2 02-Jan 0 2 2 03-Jan 0 3 2 04-Jan 2 0(or 1) 3 01-Jan 1 0(or 1) 3 02-Jan 0 1 3 03-Jan 0 2 3 04-Jan 3 0(or 1) 3 05-Jan 4 0(or 1)

So far I have tried this:

proc sort data=agents2 out= agents3
by agent_id sale_dt sale;
run;

data test;
set agents3;
retain count;
by agent_id;
if first.agent_id and sale>0 then do;
count=0;
end;
count+1;
run;

However that didnt work

I also tried this:

PROC SORT DATA= agents3 OUT=test /*NODUPKEY*/;

BY agent date sales;

RUN;

DATA test;

SET test;

RETAIN CONSEC_NUM;

BY agent date sales;

IF FIRST.salesTHEN CONSEC_NUM = 0;

CONSEC_NUM = CONSEC_NUM + 1;

RUN;

But this is not working. Does anyone have any idea how I can get this to work?

Super User
Posts: 5,881

## Re: Resetting a Counting Variable

I think that your first attempt looks promising. In what way didn't it work out?

Please provide sample data in a SAS program readable format if you wish someone to test/modify your code.

Data never sleeps
New Contributor
Posts: 2

## Re: Resetting a Counting Variable

Hi,

The count column doesnt even show up when I run the following:

proc sort data=agents2 out= agents3
(keep=agent_id &time_var. sale_dt tran_dt &var.); /*specify here what you want it to keep*/
by agent_id sale_dt tkt;
run;

data test;
set agents3;
retain count;
by agent_id;
if first.agent_id and tkt>0 then do;
count=0;
end;
count+1;
run;

I just get the agent_id, date and tkt (my number of sales variable)

Posts: 1,837

## Re: Resetting a Counting Variable

Try next code, after sorting your data:

``````data want;
set agents3;
by agent_id;
retain count;
if first.agent_id then count=0;
if sale = 0 then count+1;
else  count=0; /* do not assign count=1 */
run;

``````
Discussion stats
• 3 replies
• 268 views
• 0 likes
• 3 in conversation