Identify change in value in long dataset

Accepted Solution Solved
Reply
Regular Contributor
Posts: 150
Accepted Solution

Identify change in value in long dataset

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.


Accepted Solutions
Solution
‎05-12-2016 03:16 PM
Trusted Advisor
Posts: 1,117

Re: Identify change in value in long dataset

[ Edited ]
Posted in reply to rfarmenta

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).

View solution in original post


All Replies
Solution
‎05-12-2016 03:16 PM
Trusted Advisor
Posts: 1,117

Re: Identify change in value in long dataset

[ Edited ]
Posted in reply to rfarmenta

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).

Regular Contributor
Posts: 150

Re: Identify change in value in long dataset

Posted in reply to FreelanceReinhard

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 241 views
  • 0 likes
  • 2 in conversation