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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.