data have;
infile cards expandtabs truncover;
input Account_number Time_of_contact : datetime32. Connect_flag RPC_flag PTP_flag;
format Time_of_contact datetime32.;
cards;
12345 3NOV21:15:15:00 0 0 0
12345 6NOV21:11:36:56 0 0 0
12345 7NOV21:09:17:08 1 0 0
12345 8NOV21:14:08:11 1 1 0
12345 9NOV21:09:11:42 1 1 1
23456 2NOV21:08:37:15 0 0 0
23456 3NOV21:09:55:12 1 1 1
34567 1NOV21:09:10:10 0 0 0
34567 2NOV21:10:25:26 1 0 0
34567 3NOV21:11:55:55 1 1 0
;
proc sql;
create table want as
select *,
ifn(count(*)=sum(Connect_flag=0),0,sum(Connect_flag=0)) as Attempts_to_connect ,
ifn(count(*)=sum(RPC_flag=0),0,sum(RPC_flag=0)) as Attempts_to_RPC ,
ifn(count(*)=sum(PTP_flag=0),0,sum(PTP_flag=0)) as Attempts_to_PTP
from have
group by Account_number
order by Account_number,Time_of_contact ;
quit;
... View more
