Obsidian | Level 7

## Subtraction In a column

Hi all,

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Subtraction In a column

``````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;``````
7 REPLIES 7
Super User

## Re: Subtraction In a column

``````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;``````
Obsidian | Level 7

## Re: Subtraction In a column

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.
Super User

## Re: Subtraction In a column

@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?

Obsidian | Level 7

## Re: Subtraction In a column

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.
Super User

## Re: Subtraction In a column

@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.

Super User

## Re: Subtraction In a column

``````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;``````
Obsidian | Level 7

## Re: Subtraction In a column

Hi Ksharp,
Thanks a lot for your help.
Discussion stats
• 7 replies
• 3075 views
• 0 likes
• 3 in conversation