BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ali_far
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

7 REPLIES 7
Ksharp
Super User
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
Obsidian | Level 7
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
Super User

@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
Obsidian | Level 7
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
Super User

@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
Super User
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
Obsidian | Level 7
Hi Ksharp,
Thanks a lot for your help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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