topic Re: Retaining values when some subsequent values are missing (SAS Base 9.4) in SAS Programming

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

rogerward — Wed, 03 Apr 2024 00:15:16 GMT

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;

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

sbxkoenk — Thu, 04 Apr 2024 13:41:05 GMT

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

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

data_null__ — Thu, 04 Apr 2024 13:53:49 GMT

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;

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

rogerward — Thu, 04 Apr 2024 14:06:45 GMT

Thanks a lot, data_null__ ! It is very clever.

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

rogerward — Thu, 04 Apr 2024 14:08:06 GMT

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

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

FreelanceReinh — Thu, 04 Apr 2024 14:31:57 GMT

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;

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

mkeintz — Fri, 05 Apr 2024 00:45:33 GMT

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;