topic Resetting a Counting Variable in SAS Procedures

Resetting a Counting Variable

ADurston — Tue, 15 Aug 2017 08:21:32 GMT

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?

Re: Resetting a Counting Variable

LinusH — Tue, 15 Aug 2017 08:36:43 GMT

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.

Re: Resetting a Counting Variable

ADurston — Tue, 15 Aug 2017 08:55:09 GMT

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)

Re: Resetting a Counting Variable

Shmuel — Tue, 15 Aug 2017 10:20:48 GMT

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;