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!
... View more
