Identify change in value in long dataset

[rfarmenta]

I have a long dataset up to 30 observations per participant. I have type and date. For most participants the type will remain the same for a period of time then change to a new type. I want to indicate the date the type changes in the new variable, how would I best do that? I tried using sorting by ID, type and date then saying

if first.type then flag=1;

to flag the new date. That did not work. Any suggestions would be greatly appreciated. Here is some sample data and what I would like to see.

ID         Type       Date            Flag

0001       1          03/03/15

0001       1          03/04/15

0001       1          03/05/15

0001       2          03/06/15         1

0001       2          03/07/15

0002       1          03/03/15

0002       1          03/04/15

0002       2          03/05/15         1

0002       2          03/06/15 

0003       1          03/03/15

0003       2          03/04/15         1

0003       2          03/05/15

0004       2          03/06/15

Another option would be instead of flag being 1 it would be the date that the type switched from 1 to 2.

Any help would be greatly appreciated.

[FreelanceReinh · Accepted solution]

Hi @rfarmenta,

You were fairly close with your approach. Please note the differences below:

proc sort data=have;
by id date;
run;

data want;
set have;
by id type notsorted;
if first.type & ~first.id then flag=1;
run;

Edit:

Explanation: If you sort by id type date, you lose chronological order within id. The data step, however, focuses on changes in variable TYPE, while using the chronological order implicitly. Values of TYPE might occur in no particular order (only the groups of consecutive observations with constant TYPE and ID are important). Therefore, the NOTSORTED option of the BY statement is necessary. The first observation in each ID BY-group should not be regarded as a change in TYPE, hence the criterion ~first.id (i.e. not first.id).

[rfarmenta]

Thank you. I knew I was missing something but couldn't figure it out. Thank you for the extra explanation as well!