The below code doesnt consider subsequent blank values if there are two or more blank rows in between.
This only works on first blank row. Sorry if wasnt that clear yesterday. Can you please check this for me.
data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c
2 4 d 2
2 5 e 2
2 6 f
2 7 g
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;
run;
data want;
set have;
by id;
set /* create a "look-ahead" */
have (
firstobs=2
keep=id value
rename=(id=_id value=_value)
)
have ( /* this is needed to prevent a premature end of the data step */
obs=1
keep=id value
rename=(id=_id value=_value)
)
;
_lvalue = lag(value);
if
not (first.id or last.id) and
value = . and _lvalue ne . and _value ne .
then desired = 999;
else desired = value;
drop _:;
run;
How about this one .
data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c
2 4 d 2
2 5 e 2
2 6 f
2 7 g
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;
run;
data want;
do until(last.value);
set have;
by id value notsorted;
if first.id then first=1;
if last.id then last=1;
end;
do until(last.value);
set have;
by id value notsorted;
desired=value;
if not first and not last and missing(value) then desired=999;
output;
end;
drop first last;
run;
I changed my earlier code to SQL, as multiple comparisons over an arbitrary number of observations can be done there:
data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c
2 4 d 2
2 5 e 2
2 6 f
2 7 g
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;
proc sql;
create table want as
select
t1.*,
max(case
when t1.value ne .
then t1.value
else case
when t2.value ne . and t3.value ne .
then 999
else .
end
end) as desired
from have t1 left join have t2
on t1.id = t2.id and t1.sequence > t2.sequence
left join have t3
on t1.id = t3.id and t1.sequence < t3.sequence
group by t1.id, t1.sequence, t1.section, t1.value
;
quit;
How about this one .
data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c
2 4 d 2
2 5 e 2
2 6 f
2 7 g
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;
run;
data want;
do until(last.value);
set have;
by id value notsorted;
if first.id then first=1;
if last.id then last=1;
end;
do until(last.value);
set have;
by id value notsorted;
desired=value;
if not first and not last and missing(value) then desired=999;
output;
end;
drop first last;
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!
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.