Solved
Contributor
Posts: 73

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

[ Edited ]

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:

Accepted Solutions
Solution
‎05-20-2017 07:26 PM
PROC Star
Posts: 8,164

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

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

All Replies
Solution
‎05-20-2017 07:26 PM
PROC Star
Posts: 8,164

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

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

Contributor
Posts: 73

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

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

Frequent Contributor
Posts: 118

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

PROC SQL self-join might do what you want.

``````proc sql;
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;``````
Contributor
Posts: 73