topic SAS logic help in SAS Programming

SAS logic help

DataPanda99 — Fri, 16 Jul 2021 10:52:27 GMT

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

Re: SAS logic help

PaigeMiller — Fri, 16 Jul 2021 11:00:12 GMT

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.

Re: SAS logic help

DataPanda99 — Fri, 16 Jul 2021 11:14:00 GMT

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.

Re: SAS logic help

PaigeMiller — Fri, 16 Jul 2021 11:33:56 GMT

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;

Re: SAS logic help

DataPanda99 — Fri, 16 Jul 2021 11:53:00 GMT

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

Re: SAS logic help

PaigeMiller — Fri, 16 Jul 2021 11:56:01 GMT

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

Re: SAS logic help

DataPanda99 — Fri, 16 Jul 2021 12:03:44 GMT

Thanks a lot! Now I got it. 🙂