BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lmyers2
Obsidian | Level 7

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
art297
Opal | Level 21

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

 

 

View solution in original post

3 REPLIES 3
art297
Opal | Level 21

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
Diamond | Level 26

@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
Reeza
Super User

@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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

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