topic Re: How to check if the last observation of a group is different from the one prior to it? in SAS Programming

How to check if the last observation of a group is different from the one prior to it?

Shradha1 — Thu, 28 Jan 2021 08:09:43 GMT

I have a list of students and a list of devices they have used for various sessions, all in a sorted form:
Student Device
A. Mac
A. Tablet
A. Laptop
B. PC
B. ANDROID
B. ANDROID
C. TABLET
C. TABLET
C. IPHONE

I need to create a flag for those students for whom the device in their last session doesn't match with the device in the previous session.
So for the above data, the flag will be created for student A and C. How do I proceed? Please help

Re: How to check if the last observation of a group is different from the one prior to it?

PeterClemmensen — Thu, 28 Jan 2021 08:16:27 GMT

Try this

data have;
input Student $ Device $;
datalines;
A Mac    
A Tablet 
A Laptop 
B PC     
B ANDROID
B ANDROID
C TABLET 
C TABLET 
C IPHONE 
;

data want(drop = d);
   do until (last.Student);
      set have;
      by Student;
      d = lag(Device);
      flag = (last.Student and Device ne d);
   end;
   do until (last.Student);
      set have;
      by Student;
      output;
   end;
run;

Result:

Student  Device   flag 
A        Mac      1 
A        Tablet   1 
A        Laptop   1 
B        PC       0 
B        ANDROID  0 
B        ANDROID  0 
C        TABLET   1 
C        TABLET   1 
C        IPHONE   1

Re: How to check if the last observation of a group is different from the one prior to it?

Kurt_Bremser — Thu, 28 Jan 2021 08:26:43 GMT

data want;
set have;
by student; /* add notsorted option if necessary */
ldev = lag(device); /* keep previous value of device */
if
  last.student /* test only at last observation of student */
  and device ne ldev /* compare devices */
  and not first.student /* students with only one entry are never flagged */
then flag = 1;
else flag = 0;
drop ldev;
run;

Re: How to check if the last observation of a group is different from the one prior to it?

Astounding — Thu, 28 Jan 2021 09:33:25 GMT

Let's simplify:

data want;
set have;
by student device notsorted;
if last.student and first.device then flag=1;
run;

Re: How to check if the last observation of a group is different from the one prior to it?

Kurt_Bremser — Thu, 28 Jan 2021 10:00:56 GMT

That would flag all students who have only one entry. The condition should be

if last.student ne first.student and first.device then flag=1;

Re: How to check if the last observation of a group is different from the one prior to it?

Astounding — Thu, 28 Jan 2021 13:15:29 GMT

Good catch, @Kurt_Bremser . I could have been more careful. However, the suggestion you made won't do the job. It will flag the first observation for each student (as long as the student has more than one observation).

Here is a more careful variation:

data want;
set have;
by student device notsorted;
if last.student and first.device and not first.student then flag=1;
run;

Re: How to check if the last observation of a group is different from the one prior to it?

Kurt_Bremser — Thu, 28 Jan 2021 13:29:38 GMT

You're right. Yours is the first solution I also came up with, but then I obviously wanted to be overly cute 😉