Create variable that counts progressively how many times an ID is repeated in a day

[catkat96]

So I have a table that has different days and didfferent hours in each day, and the client ID can appear multiple times per day. 

data Data1;
   infile datalines delimiter=',';
   input date :ddmmyy10. ID $ time :time8. $ ;
   format date ddmmyy10.;  
   format time time8.; 
datalines;
05/11/2020,1000,8:15:23
05/11/2020,1000,8:20:10
05/11/2020,1001,8:21:10
05/11/2020,1001,9:05:15
05/11/2020,1001,10:30:20
06/11/2020,1002,8:26:10
06/11/2020,1003,8:27:10
06/11/2020,1003,9:40:01
;

 I want to output another column that is called "Attempts" which would look like this:

data Data1;
   infile datalines delimiter=',';
   input date :ddmmyy10. ID $ time :time8. $ Attempt;
   format date ddmmyy10.;  
   format time time8.; 
datalines;
05/11/2020,1000,8:15:23,1
05/11/2020,1000,8:20:10,2
05/11/2020,1001,8:21:10,1
05/11/2020,1001,9:05:15,2
05/11/2020,1001,10:30:20,3
06/11/2020,1002,8:26:10,1
06/11/2020,1003,8:27:10,1
06/11/2020,1003,9:40:01,2
06/11/2020,1000,10:20:10,1
06/11/2020,1000,11:20:10,2
06/11/2020,1000,12:20:10,3

06/11/2020,1000,13:20:10,3
;

As you can see, client 1000 appears two times on day 05/11 and 4 times on 06/11, customer 1001 appears 3 imes on 05/11, customer 1002 appears one time on 06/11 and customer 1003 appears 2 times on 06/11. 

I also would like that 3 is the maximum, so if a client appears more than 3 times on a day (like customer 1000 on 06/11) the value would be 3.

I don't mind using sas directly or proc sql, so if anyone has an idea in either, please let me know 🙂

[ballardw · Accepted solution]

Something like:

proc sort data=data1;
   by id date time;
run;

data want;
   set data1;
   by id date;
   retain attempt;
   if first.date then attempt=1;
   else attempt+1;
   if attempt>3 then attempt=3;
run; 

Use of BY statement in the data step creates automatic variables indicating the first or last of value group and are 1/0 (true/false) so can be used in IF as shown.

I don't know if the sort is needed but if there is any doubt about the order of values in the data then I would recommend it as the BY statement will error out if the data is not sorted in the correct order.

[catkat96]

Thank you so much @ballardw ! I do  have a question about this code after further working with it. I want to add an extra part so that when a column named "Outcome" is "Out Of Time", attempt is 0. I included it here:

DATA want;
	SET have;
	BY  ID date time;
	RETAIN attempt;
​
        if first.date then attempt=1;
        else attempt+1;
        if attempt>3 then attempt=3;
	IF Outcome ="Out Of Time" then attempt=0;
​
run;

However in some cases it happens that:

data Data1;
   infile datalines delimiter=',';
   input date :ddmmyy10. ID $ time :time8. $ Outcome $40.  Attempt;
   format date ddmmyy10.;  
   format time time8.; 
datalines;
05/11/2020,1000,8:15:23,Answered,1,
05/11/2020,1000,8:20:10,Out Of Time,0
05/11/2020,1000,8:21:10,Answered,1
05/11/2020,1000,9:05:15,Out Of Time,0
;

kinda like the attempt "counter" resets (the third line should be attempt 2, not back to 1). Do you have any idea how I can solve that?

[ballardw]

RETAIN does what the name says: keeps the last set value across data step boundaries.

So if you do not want to "reset" the counter you need to do one of several possible items:

1) assign the value to another variable

2) "hold" a value by assigning it to a different variable BEFORE the record is output and then reassign the value to attempt. Note: this means you will need to decide explicitly when all the records are output AND you have to watch the order of operations very carefully.

This may work for the example you provide:

DATA want;
	SET have;
	BY  ID date time;
	RETAIN attempt;
   if first.date then attempt=1;
	else IF Outcome ="Out Of Time" then do;
      temp=attempt;
      attempt=0;
      output;
      attempt=temp;
   end;
   else attempt+1;
   if attempt>3 then attempt=3;
   if outcome ne "Out Of Time" then  output;
   drop temp;
run;

Every additional "requirement" will add a lot time trying to figure in the order of operations, conditional assignments and can get extremely convoluted. Since attempt really should be for the same thing I find your setting of "0" very hard to interpret and would likely expect that in a separate variable but you haven't described the actual use case at all.

Sometimes doing this piece and that piece without knowing the overall goal can result in a much harder "solution" than describing what the current data is and the actual goal.

[catkat96]

Thank you! It does work perfectly now. Yes, i would've put it in the original post but wasn't aware I needed that condition before. 

So, for explanation: I have a dataset of contacts (calls) with customers. I wanted to count how many times we called a customer, but when the outcome is "out of time" we didn't really call them, the system actually got out of time to call them so they will be included in the next slot instead. So it wouldn't be fair to count it as an attempt.

That's basically it... I think what you wrote is as straightforward as it can be given what I need