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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.