As a follow-up to Recursively filling rows I have another question. I want to achieve the same (recursively replace missing values), but using panel data.
In the example below I want to replace missing values in a dataset in a way that value of var for each row with missing value equals to the value of var for previous year for the same id1 and id2 multiplied by two:
data have;
do year = 2010 to 2015;
do id1 = 1 to 3;
do id2 = 1 to 5;
year = year;
id1 = id1;
id2 = id2;
output;
end;
end;
end;
run;
data have;
call streaminit(123);
set have;
if year = 2010 then var = rand('Normal', 10, 1);
run;
So, in row 16 (year = 2011, id1 = 1, id2 = 1) I want 10.083937704 * 2 = 20.16788, then in row 31 (year = 2012, id1 = 1, id2 = 1) I want 20.16788 * 2 = 40.33575.
I've tried to combine retain and "by" but it's not working:
proc sort data = have;
by id1 id2 year;
run;
data want;
set have;
by id1 id2;
retain var;
if first.id1 and first.id2 then var = var;
else var = var*2;
run;
Many thanks in advance for any suggestions
You can't RETAIN a variable that exists in the input data set. It will always be overwritten by the incoming value when the SET statement executes. Try this instead:
data want;
retain oldvar;
drop oldvar;
set have;
by id1 id2;
if first.id1 and first.id2 then oldvar=var;
else if missing(var) then var=oldvar*2;
/* Set OLDVAR up for the next row if the value was calculated */
oldvar=var;
run;
You can't RETAIN a variable that exists in the input data set. It will always be overwritten by the incoming value when the SET statement executes. Try this instead:
data want;
retain oldvar;
drop oldvar;
set have;
by id1 id2;
if first.id1 and first.id2 then oldvar=var;
else if missing(var) then var=oldvar*2;
/* Set OLDVAR up for the next row if the value was calculated */
oldvar=var;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.