Can PROC SQL AVG function be completed using a subset of data depending on another column ?

[cfortin]

Hello,

I know there are different ways to do what I want to do but I just can manage to make it work..

I would like to do this in a proc SQL in one select statement.

I have this table named test.

I want to create a new column (D_IL8) that would be IL8 - (mean IL8 for positive outcomes)

Mean IL8 for positive outcomes = (34+23+34+28+27)/5 = 29.20

So column D-IL8 would be IL8 value for that person - 29.20

Name	IL8	Outcome	D_IL8
Marie	34,00	Positive
Martha	56,00	Negative
Angela	23,00	Positive
Jodie	34,00	Positive
Alexe	44,00	Negative
Jane	33,00	Negative
Cassandra	28,00	Positive
Olivia	27,00	Positive
Anna	41,00	Negative
Sarah	42,00	Negative

Does anyone knows how to do that ?

Thank you so much

[Reeza · Accepted solution]

proc sql;
create table want as
select *, IL8 - mean(case when Outcome="Positive" then IL8 else . end) as D_IL8
from have;
quit;

Untested, but this should work in theory.

---

@cfortin wrote:

Hello,

I know there are different ways to do what I want to do but I just can manage to make it work..

I would like to do this in a proc SQL in one select statement.

I have this table named test.

I want to create a new column (D_IL8) that would be IL8 - (mean IL8 for positive outcomes)

Mean IL8 for positive outcomes = (34+23+34+28+27)/5 = 29.20

So column D-IL8 would be IL8 value for that person - 29.20

Name	IL8	Outcome	D_IL8
Marie	34,00	Positive
Martha	56,00	Negative
Angela	23,00	Positive
Jodie	34,00	Positive
Alexe	44,00	Negative
Jane	33,00	Negative
Cassandra	28,00	Positive
Olivia	27,00	Positive
Anna	41,00	Negative
Sarah	42,00	Negative

Does anyone knows how to do that ?

Thank you so much

---

[cfortin]

Exactly !!

It's exactly what I needed, thank you so much.

I don't totally understand it though..  it's keeping only the IL8 values of outcome=positive and then it does the mean of it ?

[Reeza]

---

@cfortin wrote:

 it's keeping only the IL8 values of outcome=positive and then it does the mean of it ?

---

It essentially creates a new (invisible) column of data that's either missing or the value when positive and takes the mean of that and adds to each row.

[cfortin]

Wonderful, thank you

[novinosrin]

Hi @cfortin 

Are you asking for this

data have;
input Name :$10. IL8 	Outcome	: $12.;
cards;
Marie	34.00	Positive	 
Martha	56.00	Negative	 
Angela	23.00	Positive	 
Jodie	34.00	Positive	 
Alexe	44.00	Negative	 
Jane	33.00	Negative	 
Cassandra	28.00	Positive	 
Olivia	27.00	Positive	 
Anna	41.00	Negative	 
Sarah	42.00	Negative	 
;


proc sql;
create table want as
select *, mean(ifn(outcome='Positive',il8,.)) as d_il8 format=8.2
from have;
quit;

[cfortin]

It's working too !! thanks.

just need to add IL8 - before the mean.

[novinosrin]

An ardent follower of @SASKiwi (i call him boolean champ)  would also attempt the following

proc sql;
create table want as
select *, sum((outcome='Positive')*il8)/sum(outcome='Positive') as d_il8 format=8.2
from have;
quit;

Sir @SASKiwi   you got me addicted to booleans. I blame you for it! lol

[PaigeMiller]

An answer that doesn't require PROC SQL. (Assumes data is sorted by OUTCOME)

proc stdize data=have method=mean out=want sprefix=D_;
    by outcome;
    var il8;
run;

My problem with the SQL approaches is that the code works for the case where you only want to do this for the values where OUTCOME='POSITIVE', but you (or someone else who is reading along) will eventually want it for OUTCOME="NEGATIVE" and yes indeed, extra programming in SQL is needed to make this work, but PROC STDIZE requires no additional programming, and it works for any number of values that OUTCOME can have, even if it is more than two.

[novinosrin]

@PaigeMiller   Very elegant indeed!

[Reeza]

In that method negative values would not be subtracted from the positive mean though, so it's not quite the same calculation, though more common.

[PaigeMiller]

---

@Reeza wrote:
In that method negative values would not be subtracted from the positive mean though, so it's not quite the same calculation, though more common.

---

I don't understand this. As far as I know, negative values are handled properly by PROC STDIZE.

[Reeza]

The calculation the OP wants is ObservedValue - Mean(positiveValues) for all records wether positive or negative. Your code won't do that, the BY statement means that the Negative values would be handled independently.

[PaigeMiller]

---

@Reeza wrote:
The calculation the OP wants is ObservedValue - Mean(positiveValues) for all records wether positive or negative. Your code won't do that, the BY statement means that the Negative values would be handled independently.

---

I would not agree with this wording.

I would say that the OP wants ObservedValue - Mean (positive OUTCOMEs)

I would say that the negative OUTCOMEs would be handled independently by PROC STDIZE, which gives the proper result for the data which has OUTCOME='POSITIVE'.