topic PLease help( inter sales times) in SAS Procedures

PLease help( inter sales times)

Statsconsultancy — Mon, 15 Feb 2010 11:57:48 GMT

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

ID DATE SALES
1 03Jan2008:10:50:30 40
1 03Jan2008:10:59:01 30
1 03Jan2008:11:04:02 40
1 03Jan2008 :11:20:04 30
1 03Feb2008 :09:05:40 30
1 03Feb2008:11:06:05 30
1 04Feb2008 :10:05:40 30
1 05Feb2008:11:06:05 30
2 03Jan2008 :09:04:06 40
2 03Jan2008:10:05:08 30
2 03Jan2008 :11:09:09 40
2 03Jan2008:11:40:09 30
2 03Jan2008 :12:45:09 40
2 03Jan2008 :12:50:08 30
2 03Feb2008:09:04:09 30
2 03Feb2008 :09:56:08 30
2 03Feb2008 :10:08:03 40
2 04Feb2008 :11:08:09 30

I need to have something like this.

ID DATE SALES inter_sales_time (minutes)
1 03Jan2008:10:50:30 40 .
1 03Jan2008:10:59:01 30 9
1 03Jan2008:11:04:02 40 5
1 03Jan2008 :11:20:04 30 16
1 03Feb2008 :09:05:40 30 .
1 03Feb2008:11:06:05 30 121
1 04Feb2008 :10:05:40 30 65
1 05Feb2008:11:06:05 30 126
2 03Jan2008 :09:04:06 40 .
2 03Jan2008:10:05:08 30 61
2 03Feb2008 :10:08:03 40 68
2 04Feb2008 :11:08:09 30 128

I tried to create a variable say DATE1 = lag(DATE), and the substraye DATE1 from DATE, but it did not work. Please rescue me.

Re: PLease help( inter sales times)

sbb — Mon, 15 Feb 2010 13:45:14 GMT

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.

Scott Barry
SBBWorks, Inc.

Google advanced search argument, this topic/post:

data step programming site:sas.com

Re: PLease help( inter sales times)

DanielSantos — Mon, 15 Feb 2010 14:54:17 GMT

I would also go with Scott's suggestion.

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.

Like this:
[pre]
data OUTDATA;
length ELAPSED 8;
format ELAPSED mmss5.;
set INDATA;
by ID; * assume INDATA is sorted by ID;
drop _:;
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;
run;
[/pre]

Cheers from Portugal.

Daniel Santos @ www.cgd.pt