DATA Step, Macro, Functions and more

arithmetic operations within a variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

arithmetic operations within a variable

I have multiple observations for each ID. I also have a variable called "score". I want to make a new variable "range" based on the values in "score" variable. Range is the difference between maximum and minimum values for score for each id. Below is what I have and what I want. 

 

What I have:

 

ID      score      

1         10

1         20

1         30

2         20

2         30

3         50

3         60

 

What I want;

ID      Score     average (this variable is the difference between the last score for an ID and the first score for the same ID)

1         10              20

1         20              .  

1         30              . 

2         20              10

2         30              .

3         50              10  

3         60              .

 

Thank you for your consideration. I appreciate your help.


Accepted Solutions
Solution
‎02-11-2018 03:33 PM
Super User
Posts: 6,624

Re: arithmetic operations within a variable

[ Edited ]
Posted in reply to Sinakian1

Many things don't make sense.  Why should the difference between the first and last be the same as the difference between the min and the max?  Why should the result appear on just the first observation for each ID?  Why should the new field be named AVERAGE?

 

At any rate, here's a program that takes care of one interpretation of the question:

 

data want;

do until (last.id);

   set have;

   by id;

   min_val = min(min_val, score);

   max_val = max(max_val, score);

end;

average = max_val - min_val;

do until (last.id);

   set have;

   by id;

   output;

   average= .;

end;

drop min_val max_val;

run;

View solution in original post


All Replies
Respected Advisor
Posts: 2,802

Re: arithmetic operations within a variable

Posted in reply to Sinakian1

Use PROC SUMMARY, which computes the RANGE for you.

 

proc summary nway data=have;
    class id;
    var score;
    output out=_ranges_ range=range;
run;

Then you can merge this back to the original data set using BY ID;

--
Paige Miller
Solution
‎02-11-2018 03:33 PM
Super User
Posts: 6,624

Re: arithmetic operations within a variable

[ Edited ]
Posted in reply to Sinakian1

Many things don't make sense.  Why should the difference between the first and last be the same as the difference between the min and the max?  Why should the result appear on just the first observation for each ID?  Why should the new field be named AVERAGE?

 

At any rate, here's a program that takes care of one interpretation of the question:

 

data want;

do until (last.id);

   set have;

   by id;

   min_val = min(min_val, score);

   max_val = max(max_val, score);

end;

average = max_val - min_val;

do until (last.id);

   set have;

   by id;

   output;

   average= .;

end;

drop min_val max_val;

run;

PROC Star
Posts: 1,555

Re: arithmetic operations within a variable

Posted in reply to Sinakian1
data have;
input ID      score;
datalines;
1         10
1         20
1         30
2         20
2         30
3         50
3         60
;

proc sql;
create table want as
select *,case when score = min(score) then range(score) else .  end as range
from have
group by id
order by 1,2;
quit;
Esteemed Advisor
Posts: 5,474

Re: arithmetic operations within a variable

Posted in reply to novinosrin

Nice code @novinosrin. But, for the sake of clarity, I would prefer if you didn't use numbers in the order by clause, especially when referring to * as a column list. It forces the reader to go back to the definition of the dataset to identify the variables. 

PG
PROC Star
Posts: 1,555

Re: arithmetic operations within a variable

Thank you Sir @PGStats and my apologies to OP @Sinakian1  please see the revised:

data have;
input ID      score;
datalines;
1         10
1         20
1         30
2         20
2         30
3         50
3         60
;

proc sql;
create table want as
select *,case when score = min(score) then range(score) else .  end as range
from have
group by id
order by id,score;
quit;
Super User
Posts: 23,228

Re: arithmetic operations within a variable

Posted in reply to Sinakian1

It doesn't seem useful to have the RANGE in a single value. That's usually done when you want to use it later on in a calculation with most values. 

 

Here's an example of how you do it with average, change it to RANGE to have it work. 

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 172 views
  • 8 likes
  • 6 in conversation