Retaining values when some subsequent values are missing (SAS Base 9.4)

[rogerward]

Hi groupies,

I am struggling with this seemingly simple retain statement.

In the first data step (called HAVE), the variable COUNTER is sometimes missing. 

When it is missing, per ID, I want (as shown in the second data step) NEWCOUNTER to be equal to the retained value of COUNTER.  In other words, if COUNTER is missing, I want NEWCOUNTER to be equal to the previous value previous non-missing value of COUNTER.

The following two data steps represent what I HAVE and what I WANT.

Thanks a lot for your help! 

data have;
input id counter;
datalines;
1 1
1 2
2 1
3 1
3 .
3 2
3 .
3 .
3 3
;
run;

data want;
input id counter newcounter;
datalines;
1 1 1
1 2 2
2 1 1
3 1 1
3 . 1
3 2 2
3 . 2
3 . 2
3 3 3
;
run;

[sbxkoenk]

data have;
input id counter;
rowid=_N_;
datalines;
1 1
1 2
2 1
3 1
3 .
3 2
3 .
3 .
3 3
;
run;

proc expand data=have
            out=want
            METHOD=STEP;
  id rowid;
  convert counter = newcounter ;
run;
/* end of program */

PROC EXPAND is a SAS/ETS procedure.
Koen

[rogerward]

Thanks, Koen. I have never used PROC EXPAND. I need to expand my life.

[data_null__]

The update statement can be used to carry forward the value of counter.   

data have;
   input id counter;
   datalines;
1 1
1 2
2 1
3 1
3 .
3 2
3 .
3 .
3 3
;
run;

data want;
   if 0 then set have;
   update have(obs=0 keep=id) have(rename=(counter=newcounter));
   by id;
   set have(keep=counter);
   output;
   run;

[rogerward]

Thanks a lot, data_null__ ! It is very clever.

[FreelanceReinh]

Hi @rogerward,

You can also use the RETAIN statement:

data want;
set have;
newcounter=ifn(id=lag(id),coalesce(counter,newcounter),counter);
retain newcounter;
run;

The code can be simplified if (as in your sample data) the COUNTER of the first observation of an ID is never missing:

data want;
set have;
newcounter=coalesce(counter,newcounter);
retain newcounter;
run;

[mkeintz]

data have;
input id counter;
datalines;
1 1
1 2
2 1
3 1
3 .
3 2
3 .
3 .
3 3
;
data want (drop=_:);
  set have;
  by id;
  retain _ctr;
  if first.id=1 or counter^=. then _ctr=counter;
  else counter=_ctr; 
run;