🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 09-01-2021 10:16 PM
(597 views)
I have the following table that cumulate value along the day by id and when the treshold (-150) is exceeded i need a flag and keep it until last.id
ID | value | cumulative_value | flag |
1 | 35 | 35 | 0 |
1 | 35 | 70 | 0 |
1 | 100 | 170 | 0 |
1 | -110 | 60 | 0 |
1 | 35 | 95 | 0 |
2 | 15 | 15 | 0 |
2 | 35 | 50 | 0 |
2 | -130 | -80 | 0 |
3 | 40 | 40 | 0 |
3 | -125 | -85 | 0 |
3 | -90 | -175 | 1 |
3 | 35 | -140 | 1 |
3 | 35 | -105 | 1 |
3 | 35 | -70 | 1 |
I've tried a lot of lag and retains structures but it doesn't work.
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A straightforward approach could work as follows:
data want;
set have;
by id;
retain flag;
if first.id then flag=0;
if cumulative_value < -150 then flag=1;
run;
This assumes you know how to get the cumulative value (or perhaps it is already in your data.) If you need to compute it along the way, here's a modification that would do that:
data want;
set have;
retain flag;
if first.id then do;
flag=0;
cumulative_value=0;
end;
cumulative_value + value;
if cumulative_value < -150 then flag=1;
run;
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A straightforward approach could work as follows:
data want;
set have;
by id;
retain flag;
if first.id then flag=0;
if cumulative_value < -150 then flag=1;
run;
This assumes you know how to get the cumulative value (or perhaps it is already in your data.) If you need to compute it along the way, here's a modification that would do that:
data want;
set have;
retain flag;
if first.id then do;
flag=0;
cumulative_value=0;
end;
cumulative_value + value;
if cumulative_value < -150 then flag=1;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Worked perfectly!! Just made a change and included "by id":
data want;
set have;
by id;
retain flag;
if first.id then do;
flag=0;
cumulative_value=0;
end;
cumulative_value + value;
if cumulative_value < -150 then flag=1;
run;
thanks