DATA Step, Macro, Functions and more

Subtraction In a column

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Subtraction In a column

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

 


Accepted Solutions
Solution
‎03-31-2018 07:40 PM
Super User
Posts: 10,849

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;

View solution in original post


All Replies
Super User
Posts: 10,849

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;
Contributor
Posts: 22

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
Posts: 13,926

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?

Contributor
Posts: 22

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
Posts: 13,926

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.

 

 

 

Solution
‎03-31-2018 07:40 PM
Super User
Posts: 10,849

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;
Contributor
Posts: 22

Re: Subtraction In a column

Hi Ksharp,
Thanks a lot for your help.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 195 views
  • 0 likes
  • 3 in conversation