12-02-2016 11:43 AM
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
select groupvar, corr(var1, var2) from table
group by groupvar;
I need the correlation variable to be one column in the final aggregated dataset.
Thanks a lot!
12-02-2016 12:12 PM
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;
12-02-2016 12:17 PM
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.
12-02-2016 12:06 PM
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.