Recursively fill missing values in rows in a panel dataset

chris2377 — Mon, 12 Sep 2022 11:43:30 GMT

As a follow-up to 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

Re: Recursively fill missing values in rows in a panel dataset

SASJedi — Mon, 12 Sep 2022 12:13:52 GMT

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;

topic Re: Recursively fill missing values in rows in a panel dataset in SAS Programming

Recursively fill missing values in rows in a panel dataset

Re: Recursively fill missing values in rows in a panel dataset