I have a SAS table like below

I required the output SAS dataset as :

Can someone please help??

Thanks in advance

CodePudding user response：

If the data was sorted by descending MONTH values it would be lot easier. It is much easier to remember a value you have already seen than to predict what value you might see in the future.

First let's convert your listing into an actual dataset we can use to work with.

data have ;
  input ID $ Grp $ Month ;
cards;
. A 202201
. A 202203
1234 A 202204
. B 202201
. B 202203
AB1234 B 202204
. C 202201
. C 202203
3333 C 202204
3333 C 202205
4444 C 202206
. T 202204
. T 202205
. T 202206
. D 202201
. D 202203
A555 D 202204
. D 202205
6666 D 202206
;

Now sort it by GRP and descending MONTH and you can use the UPDATE statement to do a last observation carried forward.

proc sort data=have;
  by grp descending month;
run;

data want;
  update have(obs=0) have;
  by grp;
  output;
run;

If you want you can resort to have ascending month values.

proc sort data=want;
  by grp month;
run;

Results:

Obs      ID      Grp     Month

  1    1234       A     202201
  2    1234       A     202203
  3    1234       A     202204
  4    AB1234     B     202201
  5    AB1234     B     202203
  6    AB1234     B     202204
  7    3333       C     202201
  8    3333       C     202203
  9    3333       C     202204
 10    3333       C     202205
 11    4444       C     202206
 12    A555       D     202201
 13    A555       D     202203
 14    A555       D     202204
 15    6666       D     202205
 16    6666       D     202206
 17               T     202204
 18               T     202205
 19               T     202206

If you really have to deal with the data in the order shown then you could use a double DOW loop. The first loop to find the next non missing ID value. And the second to re-read the observations and update the ID value and write them out.

data want;
  if 0 then set have;
  do _n_=1 by 1 until(last.grp or not missing(id));
    set have ;
    by grp notsorted;
  end;
  _id = id;
  do _n_=1 to _n_;
    set have;
    id = _id;
    output;
  end;
  drop _id;
run;