DATA Step, Macro, Functions and more

Input average value of a set of variable if missing

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

Input average value of a set of variable if missing

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

 

 


Accepted Solutions
Solution
‎04-06-2017 09:40 AM
Super User
Super User
Posts: 7,392

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

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;

View solution in original post


All Replies
Solution
‎04-06-2017 09:40 AM
Super User
Super User
Posts: 7,392

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

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;
Super User
Posts: 17,750

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

[ Edited ]

@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 Smiley Happy

 

@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;
Super User
Posts: 5,072

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

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.

Super User
Posts: 17,750

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

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

Super User
Super User
Posts: 7,392

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

Its nice when these things work themselves out Smiley Very Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 160 views
  • 1 like
  • 4 in conversation