Proc SQL SAS

[tomtom09]

Can anybody help me that how I can calculate the correlation between two variables within each group in Proc Sql? Is there any such function just as sum or mean?

I want to do something like

proc sql;

select groupvar, corr(var1, var2) from table

group by groupvar;

quit;

I need the correlation variable to be one column in the final aggregated dataset. 

Thanks a lot! 

[Reeza]

You can't use SQL, you need PROC CORR. 

If there's something in PROC CORR that doesn't meet your requirements specify it and we can help you customize your output. 

[tomtom09]

Thanks! I sorted the data and name it as outsort and I used the below code to create the correlation table. However there are a lot of informaiton there. Do you guys know what's the option that restrict the results only to correlation? Thanks again!

PROC CORR DATA= outsort OUTP=outcorr noprint;

VAR var1 var2;

BY groupvar;

RUN;

[RW9]

I am not familiar with the proc corr that much, so would refer to the docs.  However one thing you can do (on all procedures) is to check out what its doing behind the scenes.  Put:

ods trace on;

Before your proc corr call, then in the log you will see all the objects that procedure creates.  (trace off turns it off again).  You can then select parts of the output by:

ods output <objectname>=<datasetname>;

Where object name comes from the log output of the objects name, and datasetname is the dataset you want to store the results in.

In this way you can pull out bits of the output from a procedure.

[tomtom09]

Thanks!

[Reeza]

Noprint doesn't suppress the output? 

Or are you referring to the outcorr dataset?

[tomtom09]

Hi I am talking about the outcorr dataset. Thank you!

[RW9]

Correlation is a more advanced stats procedure, and not in the normal list of simple aggregate functions associated with SQL.  Therefore I would say no.  Create your correlations using IML or proc corr, then merge any results you need in the orginal data back on.