Moving Correlation - For multiple variables

[pmonteiro]

Hi,

I'm trying to calculate the Daily Moving Correlation between 2 variables (A and B). For that, I'm using the following code: 

**************************************************

1)

data WORK; set WORK;
OBS = 1 ;

PRODDUR_AB = A * B;

Run;

2)

proc expand DATA = WORK OUT = WORKOUT;

convert A  = ASUM / METHOD = none TRANSFORMOUT = (movsum 21);
convert B =BSUM / METHOD = none TRANSFORMOUT = (movsum 21);

convert PRODDUR_AB = PRODDUR_ABSUM / METHOD = none TRANSFORMOUT = (movsum 21);

convert obs = N / METHOD = none TRANSFORMOUT = (movsum 21);
convert A = ASS / METHOD = none TRANSFORMOUT = (movcss 21);

convert B= BSS / METHOD = none TRANSFORMOUT = (movcss 21);

CORR_AB = (PRODDUR_ABSUM - (ASUM*BSUM)/N) / ( sqrt(ASS)*sqrt(ASS));

Run;

****************************************************

Now, I have the Daily Moving Correlation between A and B in a new column.

The problem arises when I need to find the same correlation between A and C/D/E/F/G/H and etc. And I also need to find the same correlation between B and C/D/E...

In order to avoid and coding mistakes, I dont want to replicate the same function for all the variables. I would like to build a function which I would change only the key inputs (A or B) from a function already built. 

Is that possible? 

Thanks  

[PaigeMiller · Accepted solution]

First, I think you have done a great job of using PROC EXPAND to get the moving correlation between A and B.

Next, to get the moving correlations between all pairs of variables, you need a macro with loops.

For example:

%macro movingcorr(variables=);
    %let numvar = %sysfunc(countw(&variables));
    %do i=1 %to &numvar;
         %let var1=%scan(&variables,&i,%str( ));
         %do j=1 %to &numvar;
              %if &j gt &i %then %do;
              %let var2=%scan(&variables,&j,%str( ));
              proc expand data=whatever out=out_&var1._&var2;
                    convert &var1 = v1sum /method=none transformout=(movsum 21);
                    convert &var2 = v2sum /method=none transformout=(movsum 21);
                    /* You type the rest */
               run;
               data corr_&var1._&var2;
                    set out_&var1._&var2;
                    corr_&var1._&var2 = /* You type the formula */ ;
               run;
               %end;
          %end;
     %end;
%mend;
%movingcorr(variables = a b c d e f g h)              

[PaigeMiller]

Adding for those new to writing macros:

The process which needs to be followed is that you have to create code that works (and confirm that it works by testing it) without macros and without macro variables first. @pmonteiro has provided that code. Then, and only then, can you try to turn the working code into a macro. An obvious test to make sure your macro works is to compare the non-macro code results to the macro code results.

[pmonteiro]

Hello Paige,

Your code is great. I'm only having an issue to calculate the function PRODDUR which is the multiplication between 2 variables (var1 and Var2). Such variable is important to find the correlation. I tried to use it inside the macro as following:

%macro movingcorr(variables=);
    %let numvar = %sysfunc(countw(&variables));
    %do i=1 %to &numvar;
         %let var1=%scan(&variables,&i,%str( ));
         %do j=1 %to &numvar;
              %if &j gt &i %then %do;
              %let var2=%scan(&variables,&j,%str( ));
			  %let proddur= &var1*&var2;
			 
              proc expand data=WORK.gics out=out_&var1._&var2;
                    convert &var1 = v1sum /method=none transformout=(movsum 21);
                    convert &var2 = v2sum /method=none transformout=(movsum 21);
		    convert &var1 = v1ss /method=none transformout=(movcss 21);
		    convert &var2 = v2ss /method=none transformout=(movcss 21);
		    convert &proddur = proddursum /method=none transformout=(movsum 21);
                  
               run;
               data corr_&var1._&var2;
                    set out_&var1._&var2;
                    corr_&var1._&var2 = (proddursum - (v1sum*v2sum)/21 / (sqrt(v1ss)*sqrt(v2ss));


               %end;
          %end;
     %end;
%mend;
%movingcorr(variables = v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11)   

Since I need the proddursum to calculate the correlation, as you can see, I need to find the proddur. 

However, the following erros appears for me:

149         v1*v2
              _
              22
6                                                          The SAS System                               16:10 Thursday, May 16, 2019

              200

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, -, /, :, =, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.  

ERROR 200-322: The symbol is not recognized and will be ignored.


79: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 79-322: Expecting a ).

The same error appears for all correlations (v1*v3, v1*v4...).

What would you recommend?

Thanks

Pedro 

[PaigeMiller]

You have to do this multiplication to create PRODDUR in a data step before PROC EXPAND.

data gics;
    set gics;
    proddur = &var1 * &var2;
run;

[pmonteiro]

Thank you.