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:
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
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
Thank you sir. Your code solved my problem. You have a good day!!
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;
Thank you for helping me. Your solution also works. You have a nice day!!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.