Help using Base SAS procedures

Resetting a Counting Variable

Reply
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:

AgentDateNumber of Sales
101-Jan0
102-Jan1
103-Jan3
104-Jan0
105-Jan0
201-Jan0
202-Jan0
203-Jan0
204-Jan2
301-Jan1
302-Jan0
303-Jan0
304-Jan3
305-Jan4

 

 

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):

AgentDateNumber of SalesCount
101-Jan01
102-Jan10 (or 1)
103-Jan01
104-Jan02
105-Jan03
201-Jan01
202-Jan02
203-Jan03
204-Jan20(or 1)
301-Jan10(or 1)
302-Jan01
303-Jan02
304-Jan30(or 1)
305-Jan40(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)

Trusted Advisor
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;
      
     
Ask a Question
Discussion stats
  • 3 replies
  • 268 views
  • 0 likes
  • 3 in conversation