getting a standard deviation from proc sql

[lmyers2]

Hello,

I created a new variable called "Indem" that is a mean (sum/N) in proc SQL and it works. However, I'd like to get a standard deviation from this mean. I think there is a function within proc sql that is STD but I don't see sample code for it on the SAS website. Does anyone know where it goes and how to format?

Many thanks!!!

Laura

proc sql;
create table cpc as select Paid, 
Sum(Indemnity)/count(CASE_ID) as Indem,
from datahave group by Paid;
quit; 

[art297 · Accepted solution]

SAS has functions to calculate both mean and standard deviation in sql. e.g.:

             avg(age) as Mean format 10.2, 
             std(age) as std  format 10.2 label 'Std Dev'

The following paper shows most of the others: www2.sas.com/proceedings/forum2007/072-2007.pdf

Art, CEO, AnalystFinder.com

[PaigeMiller]

---

@lmyers2 wrote:

Hello,

I created a new variable called "Indem" that is a mean (sum/N) in proc SQL and it works. However, I'd like to get a standard deviation from this mean.

---

Do you mean to say "standard deviation of the mean"??

If that's what you mean, you can program this as a simple formula, std(indemnity)/sqrt(n), or you can use PROC MEANS/PROC SUMMARY to compute the MEAN and the standard deviation of the mean (STDERR) without you having to write the formula yourself.

In fact, PROC MEANS/PROC SUMMARY is a superior method for another reason. Doing the math as you have

Sum(Indemnity)/count(CASE_ID) as Indem,

will produce the WRONG mean if there are missing values in Indemnity but not in CASE_ID, or vice versa. Your standard deviation calculation will also be wrong in this case.

[Reeza]

---

@lmyers2 wrote:

I think there is a function within proc sql that is STD but I don't see sample code for it on the SAS website. Does anyone know where it goes and how to format?

---

List of functions is here, with some examples below the list:

http://support.sas.com/kb/25/279.html