Contributor
Posts: 47

# getting a standard deviation from proc sql

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;``` ```

PROC Star
Posts: 8,163

## Re: getting a standard deviation from proc sql

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

Posts: 2,985

## Re: getting a standard deviation from proc sql

[ Edited ]

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.

--
Paige Miller
Super User
Posts: 23,677

## Re: getting a standard deviation from proc sql

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

Discussion stats
• 3 replies
• 639 views
• 3 likes
• 4 in conversation