BookmarkSubscribeRSS Feed
ADurston
Calcite | Level 5

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?

3 REPLIES 3
LinusH
Tourmaline | Level 20

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
ADurston
Calcite | Level 5

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)

Shmuel
Garnet | Level 18

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;
      
     

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3076 views
  • 0 likes
  • 3 in conversation