topic Re: Correlation in New SAS User

Correlation

DomUk — Wed, 03 Mar 2021 10:52:32 GMT

Hello,

I have 20 years of data and for every year, I estimated the correlations for all the variables using the following code.

proc corr data=... out=pearson_corr;
var x1 x2 x3 x4;
with x1 x2 x3 x4;

by year
run;

In the next step I would like to get a correlation matrix which shows the mean of this 20 correlation matrixes.

Does anyone have an idea how this is possible? Using proc means I dont get the right result.

Thanks a lot for your help!

Re: Correlation

Rick_SAS — Wed, 03 Mar 2021 11:14:41 GMT

If you have SAS/IML, you can do this in a few lines of code:

Read the data WHERE _TYPE_="CORR";
Flatten the data so that each 4x4 matrix becomes a 1x16 row vector
FInd the average of the 16 columns. This average is a 1x16 row vector.
Reshape the answer into a 4x4 matrix.

data Have;
call streaminit(12345);
do year = 1 to 20;
   do i = 1 to 100;
      x1 = rand("Normal");
      x2 = x1 + rand("Normal");
      x3 = x2 + rand("Normal");
      x4 = x3 + rand("Normal");
      output;
   end;
end;
run;

proc corr data=Have out=pearson_corr noprint;
by year;
var x1 x2 x3 x4;
with x1 x2 x3 x4;
run;

proc iml;
use pearson_corr(where=(_TYPE_='CORR'));
read all var {x1 x2 x3 x4} into X;
close;

/* "flatten" the 4x4 matrices into a 1 x 16 row vector */
Y = shape(X, 0, 16);
/* compute the mean of each column */
mean = mean(Y);
/* reshape into 4x4 matrix */
meanCorr = shape(mean, 0, 4);
print meanCorr;

If you don't have SAS/IML, you can do the same operations, but you'll need to use the DATA step and arrays to reshape the data, then use PROC MEANS to find the means of the 16-variable data.

Re: Correlation

FreelanceReinh — Wed, 03 Mar 2021 11:29:09 GMT

Hello @DomUk,

Try this:

proc summary data=pearson_corr nway;
class _name_;
var x:;
output out=want(keep=_name_ x:) mean=;
run;

[Edit: Removed redundant statement where _type_=:'C';]

Re: Correlation

DomUk — Wed, 03 Mar 2021 14:50:11 GMT

Thank you very much,
how can I keep automatically the variable names ( actually I have 30 varaibles, so to rename them step by step would be taff) and bring them in an folder, after your code they are renamed in col 1... and row1... and only shown in the result viewer.

Re: Correlation

DomUk — Wed, 03 Mar 2021 15:39:51 GMT

Thanks a lot. I get already the right numbers, however on the x-axis the varaibles are not sorted (so the diagonal "1-line" is not there, instead the 1-values are distributed thorughout the whole table.

Re: Correlation

FreelanceReinh — Wed, 03 Mar 2021 18:16:08 GMT

@DomUk wrote:
Thanks a lot. I get already the right numbers, however on the x-axis the varaibles are not sorted (so the diagonal "1-line" is not there, instead the 1-values are distributed thorughout the whole table.

I see. Your variable names are not alphabetically sorted (unlike x1, x2, x3, x4), are they? In this case you may want to retrieve their sort order from dataset pearson_corr and finally use this information to sort the output dataset from PROC SUMMARY.

As an example, let's assume the variable names are h, d, s, q.

%let vars=h d s q;

/* Compute correlation coefficients by year */

proc corr data=have out=pearson_corr noprint;
var &vars;
by year;
run;

/* Retrieve sort order of analysis variables */

data _vars(keep=_seqno _name_);
set pearson_corr(firstobs=4);
_seqno=_n_;
if _type_~=:'C' then stop;
run;

/* Compute mean values of correlation coefficients */

proc summary data=pearson_corr nway;
class _name_;
var &vars;
output out=_tmp(drop=_type_ _freq_) mean=;
run;

/* Sort the resulting dataset */

proc sql;
create table want as
select a.* from _tmp a, _vars b
where a._name_=b._name_
order by _seqno;
drop table _vars, _tmp;
quit;

EDIT: If you store the variable names in macro variable VARS (as shown above), you can also use this macro variable to obtain the desired sort order. That is, you can omit the DATA step (data _vars ...) and replace the PROC SQL step by this modified version:

proc sql;
create table want as
select * from _tmp
order by whichc(upcase(_name_),"%sysfunc(tranwrd(%upcase(&vars),%str( ),%str(",")))");
drop table _tmp;
quit;

Re: Correlation

DomUk — Wed, 03 Mar 2021 23:08:40 GMT

hmm I dont know why but this code does not work.
%let vars=earn cfo c_sales c_cogs c_oe c_int c_tax
c_othert del_ar del_inv del_ap depr amort
other_acc_s_t acc_s_t div_s_t BV_s_t at_s_t negEE_s_t NEGE DivD;

/* Compute correlation coefficients by year */

proc corr data=mylibf1.endversion out=pearson_corr noprint;
var &vars;
by houyear;
run;

/* Retrieve sort order of analysis variables */

data _vars(keep=_seqno _name_);
set pearson_corr(firstobs=21);
_seqno=_n_;
if _type_~=:'C' then stop;
run;

/* Compute mean values of correlation coefficients */

proc summary data=pearson_corr nway;
class _name_;
var &vars;
output out=_tmp(drop=_type_ _freq_) mean=;
run;

/* Sort the resulting dataset */

proc sql;
create table want as
select a.* from _tmp a, _vars b
where a._name_=b._name_
order by _seqno;
drop table _vars, _tmp;
quit;

Now I have four variables in the rows and all variables in the columns.
Sry Iam a very new user of sas.

Re: Correlation

FreelanceReinh — Wed, 03 Mar 2021 23:32:24 GMT

You changed

set pearson_corr(firstobs=4);

set pearson_corr(firstobs=21);

probably assuming that the "4" meant the number of variables -- but that was pure coincidence. The SET statement must start at the fourth observation irrespective of the number of variables.

Re: Correlation

DomUk — Fri, 05 Mar 2021 11:54:28 GMT

thank you! Now it works.
If I run the correlation by year, then I get a pvalue for every variable-combination.
Is it possible to become the average pvalues as well?

Re: Correlation

FreelanceReinh — Fri, 05 Mar 2021 12:42:44 GMT

Yes, it's possible to obtain average p-values as well.

Here's a complete example:

data have;
call streaminit(27182818);
do year=1 to 20;
  do _n_=1 to 100;
    d=rand('uniform');
    h=rand('uniform');
    s=rand('uniform');
    q=rand('uniform');
    output;
  end;
end;
run;

%let vars=h d s q; /* Variable order may be defined differently than in the dataset. */

/* Compute correlation coefficients by year */

ods output pearsoncorr=pearson_corr;
proc corr data=have;
var &vars;
by year;
run;

/* Compute mean values of correlation coefficients and p-values */

proc summary data=pearson_corr nway;
class Variable;
var _numeric_;
output out=_tmp(drop=year _:) mean=;
run;

/* Sort the resulting dataset */

proc sql;
create table want as
select * from _tmp
order by whichc(upcase(Variable),"%sysfunc(tranwrd(%upcase(&vars),%str( ),%str(",")))");
drop table _tmp;
quit;

Result:

Variable           h           d           s           q          Ph          Pd          Ps          Pq

   h         1.00000     0.00485     0.01906     0.01108       .          0.5860      0.4575      0.5198
   d         0.00485     1.00000     0.00465    -0.00434      0.5860       .          0.4407      0.4343
   s         0.01906     0.00465     1.00000    -0.01984      0.4575      0.4407       .          0.5118
   q         0.01108    -0.00434    -0.01984     1.00000      0.5198      0.4343      0.5118       .

The variable list _numeric_ in the PROC SUMMARY step includes year (which is then dropped from dataset _tmp). Alternatively, you could use a different variable list:

proc summary data=pearson_corr nway;
class Variable;
var &vars p:;
output out=_tmp(drop=_:) mean=;
run;

This works in the current example (where none of the variable names in &vars starts with "P"). In other cases you might need to construct the variable list for the VAR statement differently (e.g. similarly to the TRANWRD technique used in the PROC SQL step).

Re: Correlation

Rick_SAS — Fri, 05 Mar 2021 14:36:43 GMT

With respect, the arithmetic average p-value is a meaningless quantity. It has no useful interpretation that I am aware of.

To give an example, suppose for 19 models, the p-value is 0.01. For the 20th model, the p-value is 0.99. The average of those values is ~0.059, but that value does not mean anything because it is not the result of a statistical test.

Re: Correlation

DomUk — Fri, 05 Mar 2021 14:54:03 GMT

yes that is right, however in my data, the pvalues are almost the same. So it might be okay in this case.

Re: Correlation

DomUk — Fri, 05 Mar 2021 16:34:53 GMT

I have one small more question.

For the same 20 years I calculated descriptive statistics using the following code:

proc means data=... N Mean min p1 p25 Median p75 p99 max std;
var x1 ..... x21;
by year;
output out=desk_stat mean()= p1()= p25()= median()= p75()= p99()= std()= /autoname;
run;

In the second step I calculated again the mean of the 20 years:

proc means data=desk_stat mean;
output out = mylib.desk_stat_EV;
run;

After the first step I get a very nice picture in the result window. I would like to get such a table (columns show the statistics and row the variables) for my output.

I think I have to use a transform step, but Iam not exactly sure how I can handle it.

Thanks again!

Re: Correlation

FreelanceReinh — Fri, 05 Mar 2021 17:25:35 GMT

The statistic-keywords in the PROC MEANS statement control the output in the Output window (listing output) or Results Viewer (HTML), independently of the statistics for the output dataset requested in the OUTPUT statement. In your second PROC MEANS step you don't specify statistics in the OUTPUT statement (and use no VAR statement either) so that the output dataset will contain a default set of statistics (N, min, max, mean, std) for all numeric variables: five rows with one statistic per row. You can obtain an output dataset with columns Variable, Mean, ... (other statistics as specified in the PROC MEANS statement) ... by using an ODS OUTPUT statement in conjunction with the STACKODSOUTPUT option of the PROC MEANS statement. For example:

proc means data=desk_stat(drop=_:) mean std stackodsoutput;
ods output summary=want;
run;

proc print data=want;
run;

Re: Correlation

FreelanceReinh — Fri, 05 Mar 2021 17:27:50 GMT

@DomUk wrote:
yes that is right, however in my data, the pvalues are almost the same. So it might be okay in this case.

Indeed, certain conclusions are valid. For example:

mean(of p1-p20)<=0.0025 implies max(of p1-p20)<=0.05,
mean(of p1-p20)> 0.9525 implies min(of p1-p20)> 0.05.

Re: Correlation

DomUk — Thu, 18 Mar 2021 19:30:40 GMT

Hi again 🙂
If I run the code, I get the following note message: NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause. I dont exactly understand the meaning of this statement. Can I ignore it? The exact log statement is the following:
187 proc sql;
188 create table Avg_korr2 as
189 select * from _tmp
190 order by whichc(upcase(Variable),"%sysfunc(tranwrd(%upcase(&vars),%str( ),%str(",")))");
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: Table WORK.AVG_KORR2 created, with 21 rows and 43 columns.

191 drop table _tmp;
NOTE: Table WORK._TMP has been dropped.
192 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
cpu time 0.09 seconds

Re: Correlation

FreelanceReinh — Thu, 18 Mar 2021 19:46:44 GMT

Hi @DomUk,

Yes, you can ignore that note. It refers to the WHICHC expression (in the ORDER BY clause), which indeed "doesn't appear in [the] SELECT clause" -- but exactly this is intended.

Re: Correlation

DomUk — Sun, 02 May 2021 22:48:40 GMT

Hi again,
is it possible to run the same code for spearman correlation? If I substitute pearson corr with spearmancorr it does not work. YOu can find the code below.
Thanks again for an answer!

%let vars=earn_s_t cfo_s_t c_sales_s_t c_cogs_s_t c_oe_s_t c_int_s_t c_tax_s_t
c_other_s_t del_ar_s_t del_inv_s_t del_ap_s_t depr_s_t amort_s_t
oth_acc_s_t acc_s_t div_s_t BV_s_t at_s_t negEE_s_t NEGE DivD;

ods output SpearmanCorr=spearman_Korr;
proc corr data=mylib.endversion;
var &vars;
by houyear;
run;

proc summary data=Spearman_Korr nway;
class Variable;
var _numeric_;
output out=_tmp(drop=houyear _:) mean=;
run;

proc sql;
create table mylib.Avg_korr_Spearman as
select * from _tmp
order by whichc(upcase(Variable),"%sysfunc(tranwrd(%upcase(&vars),%str( ),%str(",")))");
drop table _tmp;
quit;

Re: Correlation

PaigeMiller — Sun, 02 May 2021 22:52:57 GMT

What doesn't work? Explain. Show us.

Re: Correlation

FreelanceReinh — Mon, 03 May 2021 09:45:10 GMT

Of course, you can apply the same techniques to Spearman correlation coefficients that we used for Pearson correlation coefficients. Just specify the appropriate option SPEARMAN in the PROC CORR statement.

proc corr data=mylib.endversion spearman;

Otherwise you will get this warning in the log:

WARNING: Output 'SpearmanCorr' was not created.  Make sure that the output object name, label, or path is spelled correctly.  Also, verify that the
         appropriate procedure options are used to produce the requested output object.  For example, verify that the NOPRINT option is not used.

The request to "verify that the appropriate procedure options are used to produce the requested output object" means: Check column "Option" in the table of ODS table names, which is found under "Details" in the procedure documentation. For PROC CORR: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/procstat/procstat_corr_details24.htm.