topic Re: arithmetic operations within a variable in SAS Programming

arithmetic operations within a variable

Sinakian1 — Sun, 11 Feb 2018 01:02:08 GMT

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.

Re: arithmetic operations within a variable

PaigeMiller — Sun, 11 Feb 2018 01:15:36 GMT

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;

Re: arithmetic operations within a variable

Astounding — Sun, 11 Feb 2018 01:17:06 GMT

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;

Re: arithmetic operations within a variable

novinosrin — Sun, 11 Feb 2018 01:36:13 GMT

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;

Re: arithmetic operations within a variable

Reeza — Sun, 11 Feb 2018 03:24:47 GMT

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

Re: arithmetic operations within a variable

PGStats — Sun, 11 Feb 2018 06:05:10 GMT

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.

Re: arithmetic operations within a variable

novinosrin — Sun, 11 Feb 2018 06:15:12 GMT

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;