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 |
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).
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).
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.