topic Re: Correlate variable from two dataset in SAS Programming

Correlate variable from two dataset

JAR — Thu, 19 Aug 2021 01:10:08 GMT

Hi,

I have two datasets

Original
Date	x	y
15-08-2021	1	7
16-08-2021	2	4
17-08-2021	3	3
18-08-2021	4	3


Final
Date	x	y
15-08-2021	8	6
16-08-2021	7	3
17-08-2021	6	2
18-08-2021	4	1

I would like to find the correlation between x of original and x of final. Similarly for each corresponding pair.

Please advise me how to do this.

Jijil Ramakrishnan

Re: Correlate variable from two dataset

ballardw — Thu, 19 Aug 2021 02:44:47 GMT

@JAR wrote:

Hi,

I have two datasets

Original

Date x y

15-08-2021 1 7

16-08-2021 2 4

17-08-2021 3 3

18-08-2021 4 3

Final

Date x y

15-08-2021 8 6

16-08-2021 7 3

17-08-2021 6 2

18-08-2021 4 1

I would like to find the correlation between x of original and x of final. Similarly for each corresponding pair.

Please advise me how to do this.

Jijil Ramakrishnan

I think you are looking for something like:

/* assumes 1) no duplicates of date
   2) both the data sets are sorted by date
*/
data need;
   merge original
         final (rename=(x=xfinal y=yfinal))
   ;
   by date;
run;

proc corr data=need;
   var x y;
   with xfinal yfinal;
run;

However if you have multiples for the date variable in both data sets you need to provide a more complete example of both data sets.

Re: Correlate variable from two dataset

JAR — Thu, 19 Aug 2021 01:54:34 GMT

Thanks this helps. However, I have over 100 variables. Is there any shortcut to
1) Rename them all
2) Pair them up in proc corr

Re: Correlate variable from two dataset

ballardw — Thu, 19 Aug 2021 02:57:28 GMT

You can use a double transpose to put a prefix in front of the variables if your variable names are not so long that additional characters won't make the names too long.

Example adding Final_ as prefix:

data junk;
  input a $ x y;
datalines;
a  1 2
b  11 22
c  111 222
;

Proc transpose data=junk  out=trans
   ;
   by a;
run;

proc transpose data=trans out=finalwant (drop=_name_)
      Prefix=Final_;
   by a;
   id  _name_;
   var col1;
run;

This will only rename numeric variables which for Proc Corr is sufficient because you cannot calculate correlation for character valued variables.

You could do the same thing, obviously using a different output name for the second Proc Transpose with a different prefix for the "original" data.

The merge would not need the rename step now.

In the Proc Corr you could use a prefix variable list in the Var and With statements. Assuming you used "Old_" as the prefix for the original data you would use

Proc corr data=merged;
   var Old_: ;
   with Final_: ;
run;

The construct Old_: with the colon immediately after a prefix tells SAS to use all the variables that start with that prefix almost any place a list of variables can appear.

Re: Correlate variable from two dataset

Tom — Thu, 19 Aug 2021 03:04:42 GMT

Do you really want to "correlate" them? Or just check for differences?

If the later look at PROC COMPARE

proc compare data=original compare=final;
  id date;
run;

Re: Correlate variable from two dataset

JAR — Thu, 19 Aug 2021 03:22:11 GMT

I do want to do statistical correlation. Thanks for your help.