Subtraction In a column

[ali_far]

Hi all,

I will appreciate your help in advance.

I have a data set like this:

Group   Var1  Value    wanted?

1           A         4          1(5-4)

1           B        5           1

2           A         6          -5 (1-6)

2           B         1          -5

3           A         2          5(7-2)

3           B        7            5

for each group : value of B - Value of A.

Can I use proc sql with condition?

Thanks

[Ksharp]

data have;
input Group   Var1 $ Value;
cards;
1           A         4          1(5-4)
1           B        5           1
2           A         6          -5 (1-6)
2           B         1          -5
3           A         2          5(7-2)
3           B        7 
;
run;
data want;
 merge have have(keep= value rename=(value=_v) firstobs=2);
 retain want;
 if group ne lag(group) then want=_v-value;
 drop _v;
run;

[ali_far]

Thanks a lot. This code works. however, It needs some changes. A and B are not in order.. some times it is B and A. but always should :value of B - Value of A.

[ballardw]

---

@ali_far wrote:
Thanks a lot. This code works. however, It needs some changes. A and B are not in order.. some times it is B and A. but always should :value of B - Value of A.

---

And how do we know the order of the subtraction needed from the values inside the data set? "Sometimes" is an awful vague requirement.

You may know the order needed because it is your data but computers are stupid and need very explicit rules to identify cases before we can write the code to implement those very explicit rules.

If the data does not contain the values needed to make that decision there were does that information reside?

[ali_far]

I really did not catch your point. I meant i want exactly subtract value of B from Value of A. However that code subtract them in order, second value minus first one.

[ballardw]

---

@ali_far wrote:
I really did not catch your point. I meant i want exactly subtract value of B from Value of A. However that code subtract them in order, second value minus first one.

---

"value of B" is not quite appropriate phrasing. You do not have a variable A or B that has values, you have a single variable named Var1 that is associated with another variable Value. Since the order of processing is needed and was not quite clearly stated the example data was processed as demonstrated and works with the example data.

One way would be to sort the data by group and var1 and then @Ksharp's solution will work as var1 value of "B" will always follow var1 value of "A". You example data implied that the rows with var1 values of B followed A.

If there is not a clear example of all of the issues possible with the starting data it can be difficult to come up with a solution that satisfies unstated requirements.

[Ksharp · Accepted solution]

data have;
input Group   Var1 $ Value;
cards;
1           A         4          1(5-4)
1           B        5           1
2           A         6          -5 (1-6)
2           B         1          -5
3           A         2          5(7-2)
3           B        7 
;
run;
proc sort data=have;by Group   Var1;run;
data want;
 merge have have(keep=Group Var1 value 
 rename=(Var1=_v1 value=_v) where=(_v1='B'));
 by group;
 retain want;
 if first.group then want=_v-value;
 drop _v _v1;
run;

[ali_far]

Hi Ksharp,
Thanks a lot for your help.