Cross multiply all the variables related to an ID with all the variables of a related ID

[nazmul]

Dear Everyone,

For the following card data, I want to first identify the other IDs' having the same year in the related ID column. Then I also want to copy the value of var1 and var2 of the related ID in Var1_R and Var2_R columns. Then I will multiply Var1 with Var1_R and Var2 with Var2_R in the Cross_Multiplication column and add the results. I just included two variables in the example. I have 45 variables, 3200 IDS and 45 years totaling 132230 observations in the original dataset. How Can I do it in SAS? I will really appreciate your help.

data have;
input id year var1 var2;
cards;
1 1980 3 2
1 1990 5 2
1 2000 5 4
1 2010 9 7
2 1980 4 2
2 1990 8 5
2 2000 4 8
3 1990 2 3
3 2010 7 2
5 1990 1 1
5 2010 9 2
run;

My results should look like the following table:

[art297 · Accepted solution]

Easy with proc sql:

proc sql noprint;
  create table want as
    select a.*,
           b.id as Related_ID,
           b.var1 as Var1_R,
           b.var2 as Var2_R,
           b.var1*a.var1+b.var2*a.var2 as Cross_Multiplication
      from have a, have b
        where a.id ne b.id and
              a.year=b.year
          order by id,year
  ;
quit;

Art, CEO, AnalystFinder.com

[nazmul]

Thank you sir. Your code solved my problem. You have a good day!!

[SuzanneDorinski]

PROC SQL self-join might do what you want.

proc sql;
  create table have_linked as
    select a.*,
           b.id as related_id,
           b.var1 as var1_r,
           b.var2 as var2_r,
           sum(b.var1*a.var1,b.var2*a.var2) as cross_multiplication
      from have a,
           have b
        where a.year=b.year and
              a.id ne b.id
           order by a.id, year, b.id;
quit;

[nazmul]

Thank you for helping me. Your solution also works. You have a nice day!!