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 |
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;
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;
@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;
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.
@Astounding You're correct, the math is the same either way.
Its nice when these things work themselves out
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.