Counting instances of 1 to 0

[PetePatel]

Hi all,

I have a large dataset with ID, month and flag (1 or 0) as shown below.

I am trying to create a count by ID (variable '1_to_0'), so that for each month I can see if the flag has moved from 1 (previous month) to 0 (current month).

Then I need to sum all instances for that ID and create a separate variable ('1_to_0_sum').

What is the most efficient way of writing this code?

Thanks in advance.

ID	Month	flag	1_to_0	1_to_0_sum
1	Mar-12	0		2
1	Apr-12	0		2
1	May-12	1		2
1	Jun-12	1		2
1	Jul-12	0	1	2
1	Aug-12	0		2
1	Sep-12	1		2
1	Oct-12	0	1	2
2	Apr-12	1		3
2	May-12	0	1	3
2	Jun-12	0		3
2	Jul-12	1		3
2	Aug-12	0	1	3
2	Sep-12	0		3
2	Oct-12	1		3
2	Nov-12	1		3
2	Dec-12	0	1	3
2	Jan-13	0		3
2	Feb-13	1		3

[Kurt_Bremser · Accepted solution]

Try this:

data want;
prev_flag = 0;
sum_one_to_zero = 0;
do until (last.id);
  set have;
  by id;
  if prev_flag = 1 and flag = 0 then sum_one_to_zero + 1;
  prev_flag = flag;
end;
prev_flag = 0;
do until (last.id);
  set have;
  by id;
  if prev_flag = 1 and flag = 0
  then one_to_zero = 1;
  else one_to_zero = .;
  output;
  prev_flag = flag;
end;
drop prev_flag;
run;

Untested, for lack of usable example data (data step with datalines).

[mkeintz]

This is a good task to wax a little didactic about using the queue-based nature of the lag function:

data want;
  set have (in=firstpass)
      have (in=secondpass);
  by id;

  if firstpass then sum_one_to_zero + (lag(flag)=1 and flag=0);
  if first.id then sum_one_to_zero=0;

  if secondpass;

  if lag(flag)=1 and flag=0 then one_to_zero=1;
  if lag(id)^=id then one_to_zero=.;
run;

The statement:

  if firstpass then sum_one_to_zero + (lag(flag)=1 and flag=0);

compares the current flag to the preceding flag, building a total of transitions from 1 to 0. Because the lag function is in the then clause, it is applied only for firstpass cases - secondpass cases never impact the queue underlying the lag function.

To avoid results from the preceding id contaminating the current id, the sum is reset to zero at the start of each ID.

  if first.id then sum_one_to_zero=0;

The third use of the lag function is more subtle.  The statement

  if lag(id)^=id then one_to_zero=.;

appear to test whether the record-in-hand is the start of an id.  So why not just use

  if first.id then one_to_zero=.;  /*Do not use this for secondpass*/

Because this part of the program only deals with second_pass observations, while the first.id condition only exists for firstpass observations.  So you basically have to realize that this part of the program is only processing groups of secondpass observations.