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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.