Conditional sum statement/group by in (PROC) SQL on SAS

Question

I am working in SAS using (PROC) SQL and I have the following data set:

ID          Date      Time    Delta_Time
 x    01/01/2019    121500             0            
 x    01/01/2019    121630           130
 x    01/01/2019    122005           375 
 x    01/01/2019    154745         32740
 x    01/01/2019    155905          1160
 y    01/04/2019    132356             0

In this example, ID x performs 5 actions on 01/01/2019. What I would like to know is for how long each ID has been performing succeeding actions as long as Delta_Time does not exceed a certain value, e.g. 1500. For this example, the result should thus look like:

ID          Date      Time  
 x    01/01/2019       505
 x    01/01/2019      1160
 Y    01/04/2019         0

I have a basic understanding of SQL but am new to do loops, data steps and if statements.

How should I go about solving this?

question from:https://stackoverflow.com/questions/66053111/conditional-sum-statement-group-by-in-proc-sql-on-sas

Answer 1

The produces the output you say you want. It would be nice to have more example data.

data t;
   infile cards firstobs=2;
   input id:$1. date:mmddyy. time delta;
   format date mmddyy.;
   datalines;
ID          Date      Time    Delta_Time
 x    01/01/2019    121500             0            
 x    01/01/2019    121630           130
 x    01/01/2019    122005           375 
 x    01/01/2019    154745         32740
 x    01/01/2019    155905          1160
 y    01/04/2019    132356             0
;;;;
   run;
proc print;
   run;
data t;
   set t;
   by id date;
   delta_time=delta;
   if first.date then g=0;
   if delta gt 1500 then do;
      g+1;
      delta_time=.;
      end;
   run;
proc print;
   run;
proc means n sum;
   class id date g;
   var delta_time;
   run;