topic Re: Identifying Changes and creating a new variable in SAS Programming

Identifying Changes and creating a new variable

RandyStan — Sat, 22 Feb 2020 04:54:14 GMT

Dear All:

My Data is as follows

ID Date VarA

1 01JAN2018 0

1 03JAN2018 0

1 21JAN2018 1

1 03FEB2018 1

1 20FEB2018 1

1 03MAR2018 0

1 15MAR2018 1

1 05APR2018 0

2 20JAN208 1

2 29JAN2018 0

2 05FEB2018 0

2 18FEB2018 1

2 22FEB2018 0

2 25FEB2018 0

I want to create a variable which tracks changes from 1 to 0 for VarA for each ID. So the data set I want is

ID Date VarA VarB

1 01JAN2018 0 0

1 03JAN2018 0 0

1 21JAN2018 1 0

1 03FEB2018 1 0

1 20FEB2018 1 0

1 03MAR2018 0 1

1 15MAR2018 1 0

1 05APR2018 0 2

2 20JAN208 1 0

2 29JAN2018 0 1

2 05FEB2018 0 1

2 18FEB2018 1 0

2 22FEB2018 0 2

2 25FEB2018 0 2

So the change occurs from 1 to 0 and I want to keep a track of the number of such changes.

Thanks so much.

Randy

Re: Identifying Changes and creating a new variable

Ksharp — Sat, 22 Feb 2020 06:08:39 GMT

data have;
input ID              Date  : $20.             VarA ;  
cards;
1              01JAN2018                   0
1              03JAN2018                   0
1              21JAN2018                   1
1              03FEB2018                   1
1              20FEB2018                   1          
1              03MAR2018                  0
1              15MAR2018                 1   
1             05APR2018                 0
2              20JAN208                   1
2              29JAN2018                 0
2              05FEB2018                 0
2              18FEB2018                1
2              22FEB2018                0
2              25FEB2018              0    
;

data want;
 set have;
 varb=0;
 if id ne lag(id) then count=0; 
 if id eq lag(id) and vara=0 and lag(vara)=1 then count+1;
 if vara=0 then varb=count;
 drop count;
run;

Re: Identifying Changes and creating a new variable

PGStats — Sat, 22 Feb 2020 19:03:42 GMT

The last value of varb is inconsistent, there is no change in vara for that observation.

Re: Identifying Changes and creating a new variable

PGStats — Sat, 22 Feb 2020 19:12:04 GMT

Assuming the last value of varb in your example should be zero :

data want;
count = 0;
do until(last.id);
	set have; by id vara notsorted;
	if first.vara and vara = 0 then do;
		count + 1;
		varb = count;
		end;
	else varb = 0;
	output;
	end;
drop count;
run;