Calculating the sum of differences based conditions

[RomanLi]

Hi all,

I am struggling to calculate the sum of differences between variables based on conditions. For instance, I have this data:

What I want to see is the sum of difference in var1 between companies in the same industry, but only considers rivals who has higher value of var1. For instance, for company 001, in 2015, it has two rivals (002 and 003) in industry 4, but I only consider 002 because 003 has lower value of var1. Therefore, the difference should be 30-25=5. The same for other companies in difference years and industries. 
Please help me which statement that I should go through!

Thank you so much for your reading,

Roman

[PaigeMiller]

Show us the desired output.

Also, you have explained what to do for company 1, but its not clear to me what you should do for the other companies, and for years that don't match across companies.

[RomanLi]

Hello,

So sorry for poor explanation!

This is the result that I want to have! The column DIF records the sum of difference between companies and their rivals who has higher values of VAR1. For example, Company 002 is the best in industry 4 (highest values of VAR1 in each year), thus I don't consider the differences in Var between 002 and other companies in industry 4 (001 and 003). Therefore, DIF of 002 is 0. Company 004 does not have rivals in industry 5, therefore DIF is 0 as well. Company 001 has only 1 "better" rival (higher  value of VAR1) in industry 4, which is 002, therefore DIF records the difference in VAR1 between 001 and 002. Meanwhile, 003 has two "better" rivals in industry 4, thus therefore DIF records the THE SUM of differences in VAR1 between 001 and 003, and between 002 and 003. 

[sbxkoenk]

Hello,

Can you provide us with the data in data step format?

Paste your code in the window that pops-up after clicking the "Insert SAS Code" icon on the toolbar (the little running man).

We cannot code against a picture.

Thanks,

Koen

[RomanLi]

Hello Koen,

So sorry because I am new the platform. This is the data:

data have;
  input ID Year Ind Var1;
datalines;
001	2015	4	25
001	2016	4	15
001	2017	4	18
001	2018	4	17
002	2015	4	30
002	2016	4	45
002	2017	4	72
002	2018	4	11
003	2015	4	10
003	2016	4	9
003	2017	4	5
003	2018	4	4
004	2015	5	40
004	2016	5	50
004	2017	5	60
004	2018	5	70;
run;

Could I mind you see the previous comments about my "want" data for detailed explaination.

Thanks so much!

Roman,

[sbxkoenk]

Hello @RomanLi ,

There's a small error in your data-step.

The semi-colon to terminate the data portion should not be on the last data-line but just below.

Like here:

data have;
  input ID Year Ind Var1;
datalines;
001	2015	4	25
001	2016	4	15
001	2017	4	18
001	2018	4	17
002	2015	4	30
002	2016	4	45
002	2017	4	72
002	2018	4	11
003	2015	4	10
003	2016	4	9
003	2017	4	5
003	2018	4	4
004	2015	5	40
004	2016	5	50
004	2017	5	60
004	2018	5	70
;
run;

I have also noticed your better explanation to @PaigeMiller .

If not answered by tomorrow by someone else, I will write the program for you.

It's Sunday-evening-dinner-time here in Western Europe.

Cheers,

Koen