data T;
input ID L EXP;
Datalines;
1 1 10
1 2 20
1 4 20
1 5 20
2 1 10
2 2 60
3 2 50
3 3 50
;
data want;
do until(last.id);
set T;
by id;
if l<=1 then flag=1;
end;
do until(last.id);
set T;
by id;
if not flag then exp=0;
output;
end;
drop flag;
run;
Hi,
with this code, this is the correct output (per ID if First.ID and L>1 all the sequential Exp updated to 0)
1 1 10
1 2 20
1 4 20
1 5 20
2 1 10
2 2 60
3 2 0
3 3 0
How can this code be amended in order if per ID the sequence of L is broken, the rest Exp to update to 0? So the output should be:
1 1 10
1 2 20
1 4 0
1 5 0
2 1 10
2 2 60
3 2 0
3 3 0
Thanks.
Manipulate flag in the second DO loop if the condition is met:
data want;
do until (last.id);
set T;
by id;
if l <= 1 then flag = 1;
end;
put flag=;
do until(last.id);
set T;
by id;
if not first.id and lag(l) ne l - 1 then flag = 0;
if not flag then exp = 0;
output;
end;
drop flag;
run;
Manipulate flag in the second DO loop if the condition is met:
data want;
do until (last.id);
set T;
by id;
if l <= 1 then flag = 1;
end;
put flag=;
do until(last.id);
set T;
by id;
if not first.id and lag(l) ne l - 1 then flag = 0;
if not flag then exp = 0;
output;
end;
drop flag;
run;
It works. Thank you!
one other option would be:
data T;
input ID L EXP;
Datalines;
1 1 10
1 2 20
1 4 20
1 5 20
2 1 10
2 2 60
3 2 50
3 3 50
;
run;
data want;
set T;
by id;
dif_L = dif(L);
if first.ID then
do;
_tmp_EXP = .;
if 1 ne L then _tmp_EXP + 0;
end;
else if dif_L ne 1 then
do;
_tmp_EXP + 0;
end;
if _tmp_EXP = 0 then EXP = 0;
drop _tmp_EXP dif_L;
run;
Bart
This works too. Thanks!
data T; input ID L EXP; Datalines; 1 1 10 1 2 20 1 4 20 1 5 20 2 1 10 2 2 60 3 2 50 3 3 50 ; data tt; set t; by id; if first.id or dif(l) ne 1 then group+1; run; data want; do until(last.group); set Tt; by group; if first.group and l<=1 then flag=1; end; do until(last.group); set Tt; by group; if not flag then exp=0; output; end; drop flag group; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.