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.
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).
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).
Thank you. I knew I was missing something but couldn't figure it out. Thank you for the extra explanation as well!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.