topic Re: Proc SQL SAS in SAS Programming

Proc SQL SAS

tomtom09 — Fri, 02 Dec 2016 16:43:06 GMT

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!

Re: Proc SQL SAS

Reeza — Fri, 02 Dec 2016 17:03:10 GMT

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.

Re: Proc SQL SAS

RW9 — Fri, 02 Dec 2016 17:06:29 GMT

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.

Re: Proc SQL SAS

tomtom09 — Fri, 02 Dec 2016 17:12:23 GMT

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;

Re: Proc SQL SAS

RW9 — Fri, 02 Dec 2016 17:17:46 GMT

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.

Re: Proc SQL SAS

Reeza — Fri, 02 Dec 2016 17:23:00 GMT

Noprint doesn't suppress the output?

Or are you referring to the outcorr dataset?

Re: Proc SQL SAS

tomtom09 — Fri, 02 Dec 2016 17:25:52 GMT

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

Re: Proc SQL SAS

tomtom09 — Fri, 02 Dec 2016 17:26:15 GMT

Thanks!