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

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

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:

Picture1.png


Accepted Solutions
Solution
‎05-20-2017 07:26 PM
Esteemed Advisor
Posts: 7,294

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

 

View solution in original post


All Replies
Solution
‎05-20-2017 07:26 PM
Esteemed Advisor
Posts: 7,294

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: 51

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!!

Contributor
Posts: 52

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;
  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;
Contributor
Posts: 51

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 141 views
  • 2 likes
  • 3 in conversation