I have the following dataset:
Group Value
A .
A .
A .
A 1
A .
B .
B .
B 1
B .
B .
For each group, I want to fill the missing values of the previous two rows with the non-missing values. Specifically, I want to get the following data:
Group Value
A .
A 1
A 1
A 1
A .
B 1
B 1
B 1
B .
B .
I hope I could explain myself 🙂
Many thanks in advance!
Best,
Khaladdin
Something like this, I think:
data want;
set have;
by group;
if first.group then
_firstobs=_N_;
if missing(value) then do _point=_N_-1 by -1 to max(_firstobs,_N_-2);
set have(keep=value) point=_point;
if not missing(value) then leave;
end;
retain _:;
drop _:;
run;
(not tested, as you did not supply test data in data step format)
Hi @Khaladdin
I wonder what yout purpose is, but the following code will solve this particular case, which looks like a test vase to me..
It demonstrates hoiw to perform look-ahead with extra set statements, and I think it could easily be modified, so it could be used in a real-life situation.
data have;
input Group$ Value;
datalines;
A .
A .
A .
A 1
A .
B .
B .
B 1
B .
B .
;
run;
data want (drop=group2 value2 group3 value3);
set have nobs=n;
if _N_ < n-1 then set have (firstobs=2 rename=(group=group2 value=value2));
if _N_ < n-2 then set have (firstobs=3 rename=(group=group3 value=value3));
if (group2 = group and value2 ne .) or (group3 = group and value3 ne .) and value = . then value = max(value2,value3);
run;
Something like this, I think:
data want;
set have;
by group;
if first.group then
_firstobs=_N_;
if missing(value) then do _point=_N_-1 by -1 to max(_firstobs,_N_-2);
set have(keep=value) point=_point;
if not missing(value) then leave;
end;
retain _:;
drop _:;
run;
(not tested, as you did not supply test data in data step format)
data have;
input Group$ Value;
datalines;
A .
A .
A .
A 1
A .
B .
B .
B 1
B .
B .
;
run;
data want;
merge have
have(keep=Group Value rename=(Group=Group1 Value=Value1 ) firstobs=2)
have(keep=Group Value rename=(Group=Group2 Value=Value2 ) firstobs=3);
Value=coalesce( Value, ifn(Group=Group1,Value1,.),ifn(Group=Group2,Value2,.) );
drop Group1 Value1 Group2 Value2;
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.