Hi All,
I am trying to implement a logic but not able to achieve the right output. I would appreciate any help.
I have a dataset which has columns: student and flag. I want to calculate consecutive_flag column which will create group of flags within the student group. an example below:
student | flag | consective_flag |
1 | 0 | 0 |
1 | 0 | 0 |
1 | 0 | 0 |
1 | 1 | 1 |
1 | 1 | 1 |
1 | 1 | 1 |
1 | 0 | 0 |
1 | 1 | 2 |
1 | 1 | 2 |
1 | 0 | 0 |
1 | 0 | 0 |
1 | 1 | 3 |
1 | 1 | 3 |
2 | 1 | 1 |
2 | 1 | 1 |
2 | 1 | 1 |
2 | 1 | 1 |
2 | 0 | 0 |
2 | 1 | 2 |
2 | 0 | 0 |
2 | 0 | 0 |
2 | 1 | 3 |
2 | 1 | 3 |
data have(drop=consective_flag);
input student flag consective_flag;
datalines;
1 0 0
1 0 0
1 0 0
1 1 1
1 1 1
1 1 1
1 0 0
1 1 2
1 1 2
1 0 0
1 0 0
1 1 3
1 1 3
2 1 1
2 1 1
2 1 1
2 1 1
2 0 0
2 1 2
2 0 0
2 0 0
2 1 3
2 1 3
;
data want;
set have;
by student flag notsorted;
retain t consective_flag ;
if first.student then call missing(consective_flag,t);
if first.flag and not flag then consective_flag=flag;
else if first.flag and flag then do;
t+flag;
consective_flag=t;
end;
drop t;
run;
data have(drop=consective_flag);
input student flag consective_flag;
datalines;
1 0 0
1 0 0
1 0 0
1 1 1
1 1 1
1 1 1
1 0 0
1 1 2
1 1 2
1 0 0
1 0 0
1 1 3
1 1 3
2 1 1
2 1 1
2 1 1
2 1 1
2 0 0
2 1 2
2 0 0
2 0 0
2 1 3
2 1 3
;
data want;
set have;
by student flag notsorted;
retain t consective_flag ;
if first.student then call missing(consective_flag,t);
if first.flag and not flag then consective_flag=flag;
else if first.flag and flag then do;
t+flag;
consective_flag=t;
end;
drop t;
run;
appreciate it so much! it works perfect.
data have(drop=consective_flag);
input student flag consective_flag;
datalines;
1 0 0
1 0 0
1 0 0
1 1 1
1 1 1
1 1 1
1 0 0
1 1 2
1 1 2
1 0 0
1 0 0
1 1 3
1 1 3
2 1 1
2 1 1
2 1 1
2 1 1
2 0 0
2 1 2
2 0 0
2 0 0
2 1 3
2 1 3
;
data want;
set have;
by student flag notsorted;
if first.student then count=0;
if first.flag and flag=1 then count+1;
if flag=1 then want=count;
else want=0;
drop count;
run;
Another great way. Thanks, this works too.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.