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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.