The reason your code produces sd_south_north=8.485 (instead of 18.475) is the same as the reason it produces sum_south_north=116 (instead of 126). That's because it is treating north+south as a single calculated value for each row. This means
It excludes row 1, because north is missing, making north+sourth missing.
It provides statistics on the sum calculated for each row, not the individual components of the sum. I.e. the standard deviation is std(40+12,50+14)= std(52,64) = 8.485.
If you really must use sql to statistically treat two columns as if they were one long column, then you will have messy expressions, like
dm 'clear out';
data have;
input id south north west east;
cards;
1 10 . 15 9
2 40 12 10 14
3 50 14 13 13
run;
proc sql;
create table want as
select *
,sum(south)+sum(north) as sum_south_north
,(sum(south)+sum(north))/(count(south)+count(north)) as mean_south_north
,sqrt(
((sum(north**2) + sum(south**2)) - (sum(north)+sum(south))**2 / (count(north)+count(south)))
/
(count(north)+count(south)-1)
) as std_south_north
from have;
quit;
For a couple of column-pairs, this might be tolerable, but if there are lots of pairs, then you are asking for trouble. You're better off reshaping the data and then generating the STD more directly.
... View more
