DATA Step, Macro, Functions and more

mean with proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

mean with proc sql

I'm wondering why when I add any of the second means, the result is zero?!

data error;
input actual predicted;
datalines;
4   5 
6   6
9   8
10  10
4   4
6   8
4   4
7   9
8   8
7   5
;
run;
proc sql;
    select mean((mean(actual) - actual)**2) format 20.3 into :adjrsq from error;
quit;

sorry if my question is very simple to solve, I'm very beginner in SAS! 


Accepted Solutions
Solution
‎10-27-2016 09:32 PM
Respected Advisor
Posts: 4,641

Re: mean with proc sql

I think what you want is:

 

proc sql;
    select mean(
        ((select mean(actual) from error) - actual)**2) format 20.3 
    into :adjrsq 
from error;
quit;

%put &adjrsq;
PG

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

Re: mean with proc sql

What is it your trying to do?  The mean function will return one record, which is the mean, and that row will also contain the value which is the same.  So say 4 is the mean value of all that data, then the line your working on looks like this:

mean=4, actual=4

so:

mean((4 - 4)**2)

Will always return 0, as 4-4 is 0.

Maybe you want to merge mean onto the data so that you have all the data and mean:

proc sql;
  create table INTER as 
  select A.*,
           B.MEAN
  from   ERROR A
  left join (select mean(ACTUAL) as MEAN from ERROR) B
  on      1=1;
  create table WANT as
  select  mean((MEAN - ACTUAL)**2) 
  into :ADJRSQ 
  from INTER;
quit;

You could shrink the code above, but I am showing the interveening step. 

Contributor
Posts: 24

Re: mean with proc sql

Dear RW9;
Thanks for your comment, I used your guideline for some other purpose, was really useful.
Super User
Posts: 5,254

Re: mean with proc sql

Just want to add that mean() with one argument will be executed as the SQL aggregate function (for all records, or per GROUP BY if specified).
With multiple arguments it's the SAS Language function that gets executed and will return the mean of the listed arguments (for each record).
Data never sleeps
Solution
‎10-27-2016 09:32 PM
Respected Advisor
Posts: 4,641

Re: mean with proc sql

I think what you want is:

 

proc sql;
    select mean(
        ((select mean(actual) from error) - actual)**2) format 20.3 
    into :adjrsq 
from error;
quit;

%put &adjrsq;
PG
Contributor
Posts: 24

Re: mean with proc sql

Thanks PG Stats
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 282 views
  • 3 likes
  • 4 in conversation