SAS logic help

[DataPanda99]

Hi Experts,

Require your help on this. I have tried my best with LAG function but didn't work for all the scenarios.

I have the TimeIn and TimeOut fields and have to create the Flag below. Thanks a lot for the help.

RowNo.	TimeIn	TimeOut	Flag
1	14SEP2020:08:02:31	14SEP2020:08:02:31	N
2	14SEP2020:08:02:53	14SEP2020:08:04:19	N
3	14SEP2020:08:03:38	14SEP2020:08:04:57	N
4	14SEP2020:08:15:25	14SEP2020:08:25:39	N
5	14SEP2020:08:20:13	14SEP2020:08:20:38	Y
6	14SEP2020:08:49:01	14SEP2020:09:50:59	N
7	14SEP2020:08:53:43	14SEP2020:09:18:27	Y
8	14SEP2020:09:17:33	14SEP2020:10:07:03	N
9	14SEP2020:09:24:44	14SEP2020:09:26:08	Y
10	14SEP2020:09:27:15	14SEP2020:10:00:23	Y
11	14SEP2020:09:43:23	14SEP2020:09:43:26	Y
12	15SEP2020:08:02:31	15SEP2020:08:02:31	N
13	15SEP2020:08:02:53	15SEP2020:08:04:19	N
14	15SEP2020:08:03:38	15SEP2020:08:04:57	N
15	15SEP2020:08:15:25	15SEP2020:08:25:39	N
16	15SEP2020:08:20:13	15SEP2020:08:20:38	Y
17	15SEP2020:08:49:01	15SEP2020:09:50:59	N
18	15SEP2020:08:53:43	15SEP2020:09:18:27	Y
19	15SEP2020:09:17:33	15SEP2020:10:07:03	N
20	15SEP2020:09:24:44	15SEP2020:09:26:08	Y
21	15SEP2020:09:27:15	15SEP2020:10:00:23	Y
22	15SEP2020:09:43:23	15SEP2020:09:43:26	Y

[PaigeMiller]

I have the TimeIn and TimeOut fields and have to create the Flag below.

Instead of us trying to figure out what is the criteria for creating the variable FLAG (and possibly getting it wrong), I am asking you to clearly state the logic that creates the proper values of FLAG.

[DataPanda99]

Apologies for not explaining it properly earlier, I will try my best to explain here..

Let's take row number 19 onwards...Call entered at 09:17:33 and call connected at 10:07:03
and the next customer (row no-20) call entered at 09:24:44 and call connected at 09:26:08 (basically call connected earlier than the previous customer who was in a queue) and flagged as Y
and the next customers (21&22) calls connected earlier than 19th customer though they called after 19th customer so flagged as Y.

[PaigeMiller · Accepted solution]

So essentially the logic is that a call connected earlier than any previous call connected, then FLAG='Y'

data have;
input Row	TimeIn :datetime18.	TimeOut	 :datetime18. Flag $;
cards;
1	14SEP2020:08:02:31	14SEP2020:08:02:31	N
2	14SEP2020:08:02:53	14SEP2020:08:04:19	N
3	14SEP2020:08:03:38	14SEP2020:08:04:57	N
4	14SEP2020:08:15:25	14SEP2020:08:25:39	N
5	14SEP2020:08:20:13	14SEP2020:08:20:38	Y
6	14SEP2020:08:49:01	14SEP2020:09:50:59	N
7	14SEP2020:08:53:43	14SEP2020:09:18:27	Y
8	14SEP2020:09:17:33	14SEP2020:10:07:03	N
9	14SEP2020:09:24:44	14SEP2020:09:26:08	Y
10	14SEP2020:09:27:15	14SEP2020:10:00:23	Y
11	14SEP2020:09:43:23	14SEP2020:09:43:26	Y
12	15SEP2020:08:02:31	15SEP2020:08:02:31	N
13	15SEP2020:08:02:53	15SEP2020:08:04:19	N
14	15SEP2020:08:03:38	15SEP2020:08:04:57	N
15	15SEP2020:08:15:25	15SEP2020:08:25:39	N
16	15SEP2020:08:20:13	15SEP2020:08:20:38	Y
17	15SEP2020:08:49:01	15SEP2020:09:50:59	N
18	15SEP2020:08:53:43	15SEP2020:09:18:27	Y
19	15SEP2020:09:17:33	15SEP2020:10:07:03	N
20	15SEP2020:09:24:44	15SEP2020:09:26:08	Y
21	15SEP2020:09:27:15	15SEP2020:10:00:23	Y
22	15SEP2020:09:43:23	15SEP2020:09:43:26	Y
;

data want;
    set have;
    retain maxtimeout;
    if _n_=1 then maxtimeout=timeout;
    if timeout>=maxtimeout then do;
        flag='N';
        maxtimeout=timeout;
    end;
    else flag='Y';
run;

[DataPanda99]

Thank you but it's slightly not working. 

I have added few more observations and when I run this it should come as N but coming in as Y.

23	15SEP2020:08:55:39	15SEP2020:09:27:12	N
24	15SEP2020:09:46:28	15SEP2020:09:46:51	N
25	15SEP2020:10:01:55	15SEP2020:10:02:30	N
26	15SEP2020:10:02:37	15SEP2020:10:03:00	N

[PaigeMiller]

Since ROW 19 was answered at 15SEP2020:10:07:03, why are any of these N? Please explain the logic.

[DataPanda99]

Thanks a lot! Now I got it. 🙂