How do I flag a "new" situation within the same id?

[tlianee]

I have a dataset where I want to "flag" every time a call is forwarded. It can be forwarded back and forth between the same departments, but have to count every time it occurs. A call can also be forwarded within the same department but then it shall not be flaged. When a call is received it gets an id that is "locked" until the call has ended.

I have tried to give the example below. The first four columns is what I already have.

id	datetime	department	count	flag
1	01-01-2020 10:00	aaa	1	1
1	02-01-2020 10:00	bbb	2	1
1	03-01-2020 10:00	ccc	3	1
1	04-01-2020 10:00	aaa	4	0
1	05-01-2020 10:00	aaa	5	1
1	06-01-2020 10:00	ccc	6	0
2	01-01-2020 10:00	bbb	1	0
2	02-01-2020 10:00	bbb	2	0
2	03-01-2020 10:00	bbb	3	0
2	04-01-2020 10:00	bbb	4	0
2	05-01-2020 10:00	bbb	5	0
3	01-01-2020 10:00	aaa	1	1
3	02-01-2020 10:00	ddd	2	0
3	03-01-2020 10:00	ddd	3	0
3	04-01-2020 10:00	ddd	4	1
3	05-01-2020 10:00	aaa	5	0

[PhilC]

LOL each transfer takes a day.  Poor customers.

Is your "Want" table accurate? I think this problem will use a LAG() function on DEPARTMENT, at least.

[ballardw]

You will have to describe in some considerable detail a couple of things. First, how to identify a "call forward". Second, why would the first entry for ID 1 and 3, assuming the time is supposed to be sequential, be flagged when it is not for the first entry for ID 2?

Any rule should describe how to use the values shown.

[gamotte · Accepted solution]

Hello,

data have;
    infile cards dlm=',';
    input id dt department $ count;

    informat dt anydtdtm20. department $3.;
    format dt datetime20. department $3.;
    cards;
1,01-01-2020:10:00:00,aaa,1
1,02-01-2020:10:00:00,bbb,2
1,03-01-2020:10:00:00,ccc,3
1,04-01-2020:10:00:00,aaa,4
1,05-01-2020:10:00:00,aaa,5
1,06-01-2020:10:00:00,ccc,6
2,01-01-2020:10:00:00,bbb,1
2,02-01-2020:10:00:00,bbb,2
2,03-01-2020:10:00:00,bbb,3
2,04-01-2020:10:00:00,bbb,4
2,05-01-2020:10:00:00,bbb,5
3,01-01-2020:10:00:00,aaa,1
3,02-01-2020:10:00:00,ddd,2
3,03-01-2020:10:00:00,ddd,3
3,04-01-2020:10:00:00,ddd,4
3,05-01-2020:10:00:00,aaa,5
;
run;

data temp;
    merge have have(firstobs=2 keep=department rename=(department=dept2));
run;

data want;
    set temp;
    by Id;

    if not last.id and dept2 ne department then flag=1;
    else flag=0;
run;

[tlianee]

@PhilC the datetimes are only examples. I have not had success with getting lag to work as the same department can be on the first line in the next id.

@ballardw it is what happens to the line if the call that should trigger a flag. Since the next line in ID1 has another deperatment the first line get a flag as this call is transfered. ID 2 all the lines are within the same department and should therefore not get a flag, which is the same with observation 2 and 3 in ID 3 where these are within the same department but the fourth should get a flag as the next line is a diffenrent department. This also means that the last observation per ID never can have a flag.

@gamotte Thanks...I'll look into your solution.

[Tom]

It is not clear to me what your logic is but your example makes it look like you want to flag the LAST observation for a department except when it is also the last observation for the ID.

data have ;
  input id datetime :$16. department :$10. count flag ;
cards;
1 01-01-2020.10:00 aaa 1 1
1 02-01-2020.10:00 bbb 2 1
1 03-01-2020.10:00 ccc 3 1
1 04-01-2020.10:00 aaa 4 0
1 05-01-2020.10:00 aaa 5 1
1 06-01-2020.10:00 ccc 6 0
2 01-01-2020.10:00 bbb 1 0
2 02-01-2020.10:00 bbb 2 0
2 03-01-2020.10:00 bbb 3 0
2 04-01-2020.10:00 bbb 4 0
2 05-01-2020.10:00 bbb 5 0
3 01-01-2020.10:00 aaa 1 1
3 02-01-2020.10:00 ddd 2 0
3 03-01-2020.10:00 ddd 3 0
3 04-01-2020.10:00 ddd 4 1
3 05-01-2020.10:00 aaa 5 0
;

data want;
  set have ;
  by id department notsorted;
  want=last.department and not last.id;
run;

proc print;
run;

proc compare data=want;
  var flag;
  with want;
run;

                                      The COMPARE Procedure
                              Comparisons of variables in WORK.WANT
                                          (Method=EXACT)

                                        Data Set Summary

                   Dataset             Created          Modified  NVar    NObs

                   WORK.WANT  30DEC20:10:38:27  30DEC20:10:38:27     6      16

NOTE: No unequal values were found. All values compared are exactly equal.