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;
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
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;
Thanks a lot, data_null__ ! It is very clever.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.