Calculating average time (duration)

[deleted_user]

Hello!
I am a SPSS user who is now using SAS for the first time. Things have been going pretty well but I am stumped with a particular issue. I am hoping some of you experts out there will have an answer and be willing to help this newbie.

I am trying to calculate the length of time (mean, median, and mode) between when a record was created ("date reported" in my database) and when that same record was returned ("date closed" in my database). The reason for the calculation is that I am trying to determine if a policy change has had an impact on the length of time it is taking people to close reports. (Ultimately I want to see if there is a correlation between length of time and outcome of these reports but that's a topic for another day.) These date fields are not "SAS" fields per se. They are generated and captured by our Oracle database and then I read the field in SAS.

I have found plenty of information on how to return records within a specific interval but, so far, I am stumped on how to do an average. I know you guys will make this look easy and I am prepared to be embarassed in the interest of getting this analysis completed.

Thanks in advance!

[DanielSantos]

You will surely find plenty of statistic procedures/functions to suite your needs.

But please be more specific about what you're trying to do.

Could you please provide some of the input data and output data you expect to get?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

[deleted_user]

Thank you for the quick reply!

Let me try to be more specific about what I am trying to do. To make this more clear, and for confidentiality reasons, I am going to use a different example than what I provided in my earlier post but this new example will speak to almost the exact same issue. My new example is, "How long are people watching a specific TV channel?"

The data tell me when a person turned on the channel and when they turned off the channel. I want to determine the average length of time viewers spent watching the channel. I want to do this for all people who watched the channel. When I pull my data, I will be pulling "All Viewers who Watched the Channel" during a specific month. I will also pull "Time the Channel was Turned On" and "Time the Channel was Turned Off." When my report is done, I want to be able to say All Viewers of this Channel watched the Channel for an Average of x Hours per Day during the Month we tracked.

Does this make it more clear? Please let me know. Thank you!

[DanielSantos]

OK.

Assuming you have already the data in one row for each person (say, PERSON_ID, TV_ON_DATETIME, TV_OFF_DATETIME), The MEANS/SUMMARY procedures (they do pretty much the same) will sum the data in the many ways you need.

See the online documentation here:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a000146728.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

[Jay]

Perhaps a key in understanding the advise given is understanding what "time" is in SAS, it's the number of durations(days, seconds,etc..) from January 1, 1960.

This note may be of some help

http://support.sas.com/kb/24/585.html

There is a lot of information on Date/Time usage in the SAS documentation, your task is a common one.

regards,

[deleted_user]

Thank you, Daniel and Jay, for your responses.

I am still having difficulty getting the return I am seeking. I'll re-post showing both my code and results next week.

Thanks again!

[sbb]

Sounds as though you want to compute the time-elapsed between the two date/time values, resulting in a duration-value. If stored in a character-format, each of the strings must be converted to a SAS DATETIME in order to work with it in expressions. And, since the two events are a point in time and in SAS are represented as seconds since 1/1/1960, you can subtract the end from the start, to arrive at a duration (number representing seconds internally to SAS). With this metric, you can compute/derive other stats, as required, and report the results as seconds, hour:min, or other "formatted" value using SAS code.

Scott Barry
SBBWorks, Inc.

About SAS Date, Time, and Datetime Values
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002200738.htm

Processing ORACLE® Dates with SAS/ACCESS® in
the UNIX® Environment - paper may or may not be useful
http://support.sas.com/techsup/technote/ts566d.pdf

[deleted_user]

here's my quick friday stab at this.

[pre]
/* set up some artificial data */
data EventData;
format EventID 8. EventOpenDT EventCloseDT datetime.;
do EventID=1 to 1000;
EventOpenDT =dhms('01jul2009'd, 0, 0, floor(ranuni(123)*24*60*60));
EventCloseDT=dhms('02jul2009'd, 0, 0, floor(ranuni(456)*24*60*60));
output;
end;
run;

/* calculate duration for each record in raw data*/
data EventData;
set EventData;
EventDuration=intck('dthour', EventOpenDT, EventCloseDT);
run;

/* summarize the duration variable */
proc univariate data=eventdata noprint;
var eventduration;
output out=EventSummary n=N mode=Mode mean=Mean median=Median;
run;
proc print noobs; run;
[/pre]

result
[pre]
N Mean Median Mode
1000 23.945 24 25
[/pre]

[deleted_user]

Thank you, Scott and RichardK!

I was out Thursday and Friday and so just saw your responses this morning. I am thrilled. Both of you have given me something I think I can really work with to get the result I need. As I mentioned, I'm a fairly new user to SAS so it may take me a bit to get all this to work for me but I am confident your suggestions will get me there. If not, well, expect to see another post...

Thanks again!