complied data

[bikash10]

Hello,

I am using the SAS to sort this out. I have the data with id1 and id2 are interchangeable, it means the first row and second row is the same person becuase it has the id of 403/404. The data I want is given below by sum of rows that have id1 in id2 as well. It means first two rows should be added to get total salary based on ids. Any help is highly appreciated. 

Data have
id1	id2	salary
403	404	100.00
404	403	100.00
405	410	500.00
410	405	100.00
data want
403	404	200.00
405	410	600.00

[PaigeMiller]

Use CALL SORT to sort ID1 and ID2. Then use PROC SUMMARY to compute the sums.

[ballardw]

A few questions: Are Id1 and Id2 both of the same data type? i.e. Both actually numeric or both character? If they are both character are the variables defined to be the same length?

The answers are important as the approach @PaigeMiller indicates, to get the values in the same order on all the observations, needs some details. If the values are not both numeric or character then there would be a required step to convert one to the other so the sort is consistent. If the lengths of character values are not the same you might end up trying to stuff a 5 character value into 3 spaces and lose data from the sort step.

If the id values are both numeric or character of the same length then one of the two data steps below. The Call SortN is for numeric values, Call SortC is for character values.

data need;
   set have;
   call sortN(id1,id2);
run;
/* if both character and the same length*/
data need;
   set have;
   call sortC(id1,id2);
run;

proc summary data=need nway;
   class id1 id2;
   var salary;
    output out=want (drop=_:) sum=;
run;

Caution: if you have character values and some of them have leading spaces and others don't you may have more work to remove the leading spaces.

If the character values are different lengths we need more info as to the properties of YOUR variables.

[awesome_opossum]

proc sql; create table id1
as select
id1, 
salary
from data; 
quit; 

proc sql; create table id2
as select 
id2,
salary
from data; 
quit; 

proc sql; create table wide
as select
a.*, 
b.salary as salary2, 
(a.salary + b.salary) as total_salary

/* optionally:  ,b.id2 */
from id1 a
left join id2 b
    on a.id1 = b.id2; 
quit; 

There is probably an easier, less wonky way to do this, but here is a step by step approach that should work. This also assumes both ID's are the same format (num/char).