Proc SQL SAS

Reply
Occasional Contributor
Posts: 10

Proc SQL SAS

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! 

 

Grand Advisor
Posts: 17,361

Re: Proc SQL SAS

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. 

Occasional Contributor
Posts: 10

Re: Proc SQL SAS

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;

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,229

Re: Proc SQL SAS

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.

Occasional Contributor
Posts: 10

Re: Proc SQL SAS

Thanks!
Grand Advisor
Posts: 17,361

Re: Proc SQL SAS

Noprint doesn't suppress the output? 

 

Or are you referring to the outcorr dataset?

Occasional Contributor
Posts: 10

Re: Proc SQL SAS

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,229

Re: Proc SQL SAS

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.

Ask a Question
Discussion stats
  • 7 replies
  • 210 views
  • 0 likes
  • 3 in conversation