turn on suggestions

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

Showing results for

Find a Community

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

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2018 06:57 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to lmyers2

02-10-2018 07:35 PM

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

Highlighted
## Re: getting a standard deviation from proc sql

[ Edited ]
Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to lmyers2

02-10-2018 08:10 PM - edited 02-11-2018 07:52 AM

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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to lmyers2

02-10-2018 10:27 PM

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: