Replace missing values with multiple values

[Jarious]

I have been struggling with this.  I have sequential data that is missing some rows...and I need to fill them in.

I have:

ColA  ColB  ColC  Diff

A           B       C      1

A           B       C      2

A           B       C      4

D           E       F      1

D           E       F      3

D           E       F       4

I want:

ColA  ColB  ColC  Diff

A           B       C      1

A           B       C      2

A           B       C      3

A           B       C      4

D           E       F      1

D           E       F      2

D           E       F      3

D           E       F      4

[PaigeMiller]

It's tough to generalize from your example.

Do you always want 4 rows? Or do you want the number of rows to be flexible based on the input data?

[Jarious]

Flexible ..the length could go on for a while.  I wanted to show a simple example.  I have many missing rows and need to fill them in with the information from the previous row.

[Kurt_Bremser · Accepted solution]

See this:

data have;
input ColA $ ColB $ ColC $ Diff;
cards;
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
;
run;

data want (drop=ldif);
set have;
by cola colb colc;
ldif = lag(diff);
if not first.colc then do diff = ldif + 1 to diff;
  output;
end;
else output;
run;

[Astounding]

I think this is what you are asking for:

data want;

set have;

prior_a = lag(a);

prior_b = lag(b);

prior_c = lag(c);

skip = dif(diff);

if skip <= 1 then output;

else do;

   temp_a = a;

   temp_b = b;

   temp_c = c;

   a = prior_a;

   b = prior_b;

   c = prior_c;

   do diff = (diff - skip + 1) to (diff - 1);

      output;

   end;

   a = temp_a;

   b = temp_b;

   c = temp_c;

   output;

end;

drop prior_: temp_: skip;

run;

It's untested, so if it needs a little tweaking just ask.

[novinosrin]

@Jarious What about the possibility of :

I have:

ColA  ColB  ColC  Diff

A           B       C      1

A           B       C      2

A           B       C      4

D           E       F      1

D           E       F      3

D           E       F       4

T           U       V       1 /*is this possible ?*/

T           U       V       5/*is this possible ?*/

[novinosrin]

data have;
input ColA $ ColB $ ColC $ Diff;
cards;
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
T U V 1 
T U V 5
;
run;

proc sort data=have out=_have ;
by ColA ColB ColC descending diff;
run;

data want;
set _have;
by ColA ColB ColC;
if first.ColA and first.Colb  and first.Colc then  do diff=1 to diff;
output;
end;
else return;
run;

[Ksharp]

data have;
input ColA $ ColB $ ColC $ Diff;
cards;
A B C 1
A B C 2
A B C 4
D E F 1
D E F 3
D E F 4
;
run;
data want;
 merge have have(rename=(cola=_cola colb=_colb colc=_colc diff=_diff) firstobs=2);
 output;
 if cola=_cola and colb=_colb and colc=_colc  then do;
  do i=diff+1 to _diff-1;
   diff=i;output;
  end;
 end;
 drop _: i;
run;