topic Re: Input average value of a set of variable if missing in SAS Programming

Input average value of a set of variable if missing

Demographer — Thu, 06 Apr 2017 12:18:19 GMT

Hello,

I have a dataset that look like this. Variable AT, BE, and so on are countries. For countries where the value is missing, I would like to input the average value of non-missing countries. So for the first line, the value for BG should change to the average of -2.1343, -1.23434, -2.4543, etc. How can I do that?

variable	class1	class2	sex	AT	BE	BG	CY	CZ	DE
Age	1	0	0	-2.13432	-1.23434	.	-2.4543	-1.324	-3.123
AGE	2	0	0	.	-2.76	-2.4512	-1.126	-0.642	-3.536

Re: Input average value of a set of variable if missing

RW9 — Thu, 06 Apr 2017 12:47:27 GMT

Post test data in the form of a datastep.

As such, just a guess:

data want;
  set have;
  array vals{*} at be bg cy cz de;
  do i=1 to dim(vals);
    if vals{i}=. then vals{i}=mean(of vals{*});
  end;
run;

Re: Input average value of a set of variable if missing

Reeza — Thu, 06 Apr 2017 13:22:08 GMT

@RW9 order of operations. If multiple values are missing the mean will change as you fill array in?

Precalculate the mean?

Edit nvm - math is the same 🙂

@Demographerplease post sample data as the first data step in my code.

data have;
input id var1-var8;
cards;
1 2 4 5 78 89 34 . . 39
2 4 9 78 34 98 . 34 . 29
;
run;

data want;
set have;
array v(8) var1-var8;
x = mean(of var1-var8);

do i=1 to 8;
if v(i) = . then v(i) = mean( of var1-var8);
end;

run;

Re: Input average value of a set of variable if missing

Astounding — Thu, 06 Apr 2017 13:21:31 GMT

An interesting consideration, but I think you get the same answer either way. If you add an extra country to the mix, and its value is the same as the mean of the others, the mean shouldn't change. Probably faster to pre-calculate though.

Re: Input average value of a set of variable if missing

Reeza — Thu, 06 Apr 2017 13:39:22 GMT

@Astounding You're correct, the math is the same either way.

Re: Input average value of a set of variable if missing

RW9 — Thu, 06 Apr 2017 13:47:57 GMT

Its nice when these things work themselves out