BookmarkSubscribeRSS Feed
Statsconsultancy
Fluorite | Level 6
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.
2 REPLIES 2
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
DanielSantos
Barite | Level 11
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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1011 views
  • 0 likes
  • 3 in conversation