Create Variable That Counts Progressively How Many Times An Id Is Repeated In A Day

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=','; i

Solution 1:

Just use BY group processing and a retained variable. You can use the MIN() function to cap the counter at 3.

data Data1;
   infile datalines dsd;
   input date :ddmmyy. ID $ time :time. expect;
   format date ddmmyy10. time time8.; 
datalines;
05/11/2020,1000,8:15:23,105/11/2020,1000,8:20:10,205/11/2020,1001,8:21:10,105/11/2020,1001,9:05:15,205/11/2020,1001,10:30:20,306/11/2020,1000,10:20:10,106/11/2020,1000,11:20:10,206/11/2020,1000,12:20:10,306/11/2020,1000,13:20:10,306/11/2020,1002,8:26:10,106/11/2020,1003,8:27:10,106/11/2020,1003,9:40:01,2
;

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

Note: I reordered your sample data to avoid needing to add a PROC SORT step. If your real dataset is grouped but not sorted, as in your example, you can add the NOTSORTED keyword on the BY statement and the logic will work.

Solution 2:

Alternatively, you can still consider proc sql using a conditional correlated aggregate query:

data Data1;
   infile datalines delimiter=',';
   input date :ddmmyy10. ID $ time :time8. ;
   format date ddmmyy10.;  
   format time time8.; 
datalines;
05/11/2020,1000,8:15:2305/11/2020,1000,8:20:1005/11/2020,1001,8:21:1005/11/2020,1001,9:05:1505/11/2020,1001,10:30:2006/11/2020,1002,8:26:1006/11/2020,1003,8:27:1006/11/2020,1003,9:40:0106/11/2020,1000,10:20:10,106/11/2020,1000,11:20:10,206/11/2020,1000,12:20:10,306/11/2020,1000,13:20:10,3
;

proc sql;
    CREATE TABLE output ASSELECT d.date, d.ID, d.time
          , (SELECTCASEWHEN COUNT(*) > 3THEN3ELSE COUNT(*) 
                    ENDFROM Data1 subWHEREsub.ID = d.ID
               ANDsub.date = d.dateANDsub.time <= d.time
            ) AS attempts
    FROM Data1 d; 
quit;