I need compute the time elapsed since the previous sale made by the same agent, whenever the previous sale was made on the same day. If Ithere is no previous sale on the same day, I need to compute the time since the start of work. The work begins at 9:00am. My data looks like as follows
Using a SAS DATA step on your already sorted file, one technique is to have a SET and a corresponding BY statement to keep track of your FIRST.ID condition, and a RETAIN statement to track the prior observation (within the same ID group) and retain the DATEPART of the date/timestamp you have.
With that processing, then you can do your computation/comparison for your retained date and the current observation's date-portion of the timestamp -- then output your resulting observation. For your computed elapsed duration, use a SAS FORMAT statement to display the result in minutes format.
You just need to retain the previous SALES date/time and subtract it to the next element on the group (ID). Then reset the retained value to 9:00am.
length ELAPSED 8;
format ELAPSED mmss5.;
by ID; * assume INDATA is sorted by ID;
retain _AUX 8; * retain auxiliary var;
if first.ID then _AUX=dhms(datepart(DATE),9,0,0); * reset auxiliary var;
ELAPSED=DATE-_AUX; * calculate elapsed time;
_AUX=DATE; * store current time for next iteration;