topic Re: count average time interval in SAS Programming

count average time interval

Geo- — Fri, 17 Aug 2018 02:39:11 GMT

this might be tricky in sas,how to count times for each average time interval

below is the case for mssql,and could create a colum interval_time for each customer then sum up.how to achive it in sas？data step or proc sql

    CREATE TABLE customer_data (customer_id BIGINT, date DATE, time time, answer VARCHAR(100), missed_call_type VARCHAR(100));
    
    INSERT INTO customer_data
    VALUES
    (101, '2018/8/3', '12:13:00', 'no', 'employee'),
    (102, '2018/8/3', '12:15:00', 'no', 'customer'),
    (103, '2018/8/3', '12:20:00', 'no', 'employee'),
    (102, '2018/8/3', '15:15:00', 'no', 'customer'),
    (101, '2018/8/3', '18:15:00', 'no', 'employee'),
    (105, '2018/8/3', '18:18:00', 'no', 'customer'),
    (102, '2018/8/3', '19:18:00', 'no', 'employee')
    
    select cd.customer_id, answer, missed_call_type,
    		CAST(CAST(cd.date as VARCHAR(10))+' ' +CAST(cd.time as VARCHAR(10)) as datetime) as date,
    		ROW_NUMBER() OVER(PARTITION BY cd.customer_id ORDER BY date desc, time desc) as ranks
    INTO #temP
    from customer_data cd
    order by cd.customer_Id, ranks;
    
    select AVG(DATEDIFF(MINUTE, x1.date, x2.date)) as avg_mins
    from #temP x1
    INNER JOIN #temP x2 ON x1.customer_id = x2.customer_id 
    WHERE x2.ranks = (x1.ranks-1)

-------------------------------------------------------------------------update----------------------------------------------------------------------------------------

cst_id	minutes	time_interval(logic for calculate it)
101	362	18:15:00-12:13:00=6hours and 2mintes
102	423	[(15:15:00-12:15:00)+(19:18:00-15:15:00)]/2=7hours 3minutes

this is the procss,if there is anything unclear please tell.thanks!

Re: count average time interval [how to improve your question]

Community_Guide — Fri, 17 Aug 2018 02:06:47 GMT

Hello @Geo-,

Your question requires more details before experts can help. Can you revise your question to include more information?

Review this checklist:

Specify a meaningful subject line for your topic. Avoid generic subjects like "need help," "SAS query," or "urgent."
When appropriate, provide sample data in text or DATA step format. See this article for one method you can use.
If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message. From there you can adjust the title and add more details to the body of the message. Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @PGStats

Re: count average time interval

PGStats — Fri, 17 Aug 2018 02:51:16 GMT

Any calculation that relies on data ordering is simpler with a datastep in SAS:


data cd;
set customer_data;
date = dhms(date, 0, 0, time);
format date datetime17.;
drop time;
run;


proc sort data=cd; by customer_id date; run;

data avgTimes;
do nbInt = 1 by 1 until (last.customer_id);
    set cd; by customer_id;
    if first.customer_id then firstTime = date;
    if last.customer_id then lastTime = date;
    end;
if nbInt > 1 then do;
    totTime = lastTime - firstTime;
    avgTime = totTime / (nbInt - 1);
    output;
    end;
format totTime mmss8. avgTime time9.;
drop nbInt lastTime firstTime date;
run;

Re: count average time interval

Geo- — Fri, 17 Aug 2018 03:39:11 GMT

data step is a good way for its speed and functionality compared with sql.thanks you teacher！

Re: count average time interval

PGStats — Fri, 17 Aug 2018 04:03:08 GMT

Come to think of it, this problem doesn't rely on ordering. It can also be solved simply with SQL:

data cd;
set customer_data;
date = dhms(date, 0, 0, time);
format date datetime17.;
drop time;
run;

proc sql;
create table avgTimes as
select 
    customer_id,
    range(date) as totTime format=mmss8.,
    range(date) / (count(date)-1) as avgTime format=time9.
from cd
group by customer_id
having count(date) > 1;
quit;