Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Analytics
- /
- Stat Procs
- /
- getting a standard deviation from proc sql

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 02-10-2018 06:57 PM
(22481 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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:

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.