Check number of times a variable changes its value

[newboy1218]

Hi.. I have a dataset with 3 columns. The data is group by account ID and I want to see how many times, the variable "value" changes per account ID. I tried to use if first. and lag, but it always gives me value_change = 1 for the first record. Does anyone have a better idea to do this? Thanks.

account    var1_value    value_change

1               1                    0

1               0                    1

1               0                    0

1               1                    1

2               0                    0

2               0                    0

2               0                    0

3               0                    0

3               1                    1

[PaigeMiller · Accepted solution]

You're probably very close, however this should work

data want;
    set have;
    by account;
    lag_var1_value=lag(var1_value);
    if first.account then value_change=0;
    else value_change=(var1_value^=lag_var1_value);
    drop lag_var1_value;
run;

[newboy1218]

Amazing. Thank you so much.

[novinosrin]

data have;
input account    var1_value;*    value_change;
cards;
1               1                    0
1               0                    1
1               0                    0
1               1                    1
2               0                    0
2               0                    0
2               0                    0
3               0                    0
3               1                    1
;

data want;
set have;
by account;
value_change=ifn(not first.account,var1_value ne lag(var1_value),0);
run;

[newboy1218]

Amazing. Thank you so much. This is a much cleaner.

[Tom]

If you don't want to count the first value for an ID as a change then don't.

data want;
  set have;
  by id var1_value notsorted;
  value_change = first.var1_value and not first.id;
run;

You can also use LAG() to check for changes.

data want;
  set have;
  by id ;
  value_change = (var1_value ne lag(var1_value)) and not first.id;
run;

or

data want;
  set have;
  value_change = (var1_value ne lag(var1_value)) and (id=lag(id));
run;

[newboy1218]

Amazing. Thank you so much. It is great to have a few options to solve this issue.