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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: