topic Check number of times a variable changes its value in SAS Programming

Check number of times a variable changes its value

newboy1218 — Thu, 10 Oct 2019 15:32:15 GMT

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

3 0 0

3 1 1

Re: Check number of times a variable changes its value

PaigeMiller — Thu, 10 Oct 2019 15:42:58 GMT

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;

Re: Check number of times a variable changes its value

novinosrin — Thu, 10 Oct 2019 15:41:18 GMT

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;

Re: Check number of times a variable changes its value

Tom — Thu, 10 Oct 2019 15:47:38 GMT

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;

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

Re: Check number of times a variable changes its value

newboy1218 — Thu, 10 Oct 2019 16:15:53 GMT

Amazing. Thank you so much.

Re: Check number of times a variable changes its value

newboy1218 — Thu, 10 Oct 2019 16:16:08 GMT

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

Re: Check number of times a variable changes its value

newboy1218 — Thu, 10 Oct 2019 16:16:30 GMT

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