Obtain the mean from two or more columns using SQL and put it as a new column

[Tom]

The code you posted will not work (at least for the way you explained the problem).

The problem is here:

sum(south + north) as sum_south_north

At least one of your example observations had a missing value for one of those two variables.  So the addition operator will return a missing value.  Meaning that the non-missing value will not contribute to the total.

If that is what you wanted then your explanation of what you wanted needs to be re-worded.

[jonatan_velarde]

sure!

[Tom]

If you did want to ignore  observations where at least one of the variables is missing then you could just use:

mean(south + north)

Possibly dividing by 2 to account for the fact that two values were used to calculate each value passed to the MEAN() aggregate function.

[Ksharp]

data have;
  input id South North east west ;
cards;
1 2 4 12 9
2 3 . 11 10
3 2 4 12 10
4 6 4 13 12
;
proc sql;
create table want as
select id,South ,North ,
(select mean(South) from (select South  from have union all select North from have)) as mean_south_north	,
(select std(South) from (select South  from have union all select North from have)) as SD_south_north,
east,west,
(select mean(east) from (select east  from have union all select west from have)) as mean_east_west	,
(select std(east) from (select east  from have union all select west from have)) as SD_east_west
from have;
quit;

[jonatan_velarde]

very impressive coding, thank you very much for it!!

Now my question would be how to apply your code to more than 2 variables.

Can it be possible??

thanks in advance

[Ksharp]

Yes. You can. Just list all these variable in sub-query of PROC SQL.

proc sql;
create table want as
select id,South ,North ,East,West,

(select mean(South) from (
select South  from have 
union all 
select North from have
union all 
select East from have
union all 
select West from have
)) as mean	,
(select std(South) from (
select South  from have 
union all 
select North from have
union all 
select East from have
union all 
select West from have
)) as std

from have;
quit;

[jonatan_velarde]

dear Ksharp

Thank you for your accurate and very incredible code.

Let me offer my apologize for not let you have the answer at first time, but you deserve all credits, i will use tour code as source to continue this programming..

You understood correctly the answer, and gave the more accurate answer.

thank you and hope to read your coding every time I'm here looking for solutions from experts, that you are.

thank you very much