Creating missing rows from previous row with forward/backward method

Question

Assume that you have a table with user name, counter and score for each counter.

data have;
input user $ counter score;
cards;
A 1 50
A 3 30
A 6 90
B 1 20
B 4 20
;
run;

Some scores are missing beween some counters, and you want to put the same score as previous counter. So the result will look like below:

A 1 50
A 2 50
A 3 30
A 4 30
A 5 30
A 6 30
B 1 20
B 2 20
B 3 20
B 4 20

I tried to solve it with lag and if first.user then but it is jumping to the counter 3 after counter 1 like below:

data have_new;
set have;
by user;
if first.user then do;
x = counter;
y = score;
end;

else do;
counter = x +1;
score = y;
end;
run;

I can't come up with a solution.

Answer 1

I see this as a look-ahead problem. You can you merge with firstobs=2 to look ahead to see what the value of counter on the next record is.

Below uses a trick I think I learned from one of Mark Keintz's many lag and lead papers (e.g. http://support.sas.com/resources/papers/proceedings16/11221-2016.pdf). Where use an extra SET statement with a BY statement to make first. and last. variables.

data want;

  *This SET statement with BY statement is just to have by group processing;
  set have(keep=user);
  by user;

  *Look ahead;
  merge have have(firstobs=2 keep=counter rename=(counter=_NextCounter));

  output;

  *If there is a gap between the counter of this record and the next counter;
  *increment the counter and output;
  if last.user=0 then do while(counter ne _NextCounter-1);
    counter=counter+1;
    output;
  end;

  drop _:;
run;