Quartz | Level 8

## Counting instances of 1 to 0

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?

 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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Counting instances of 1 to 0

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

2 REPLIES 2
Super User

## Re: Counting instances of 1 to 0

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

PROC Star

## Re: Counting instances of 1 to 0

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 2 replies
• 714 views
• 2 likes
• 3 in conversation