Filling up missing values

[Khaladdin]

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

[ErikLund_Jensen]

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;

[Khaladdin]

Thank you very much! It works too. However, the next code works more efficiently in terms of timing.

[s_lassen · Accepted solution]

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)

[Khaladdin]

Thanks!

[Ksharp]

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;