topic Re: Can PROC SQL AVG function be completed using a subset of data depending on another column ? in SAS Data Management

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

cfortin — Thu, 11 Jul 2019 16:14:06 GMT

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

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

Reeza — Thu, 11 Jul 2019 16:29:12 GMT

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

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

novinosrin — Thu, 11 Jul 2019 16:29:35 GMT

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;

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

novinosrin — Thu, 11 Jul 2019 16:36:53 GMT

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

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

cfortin — Thu, 11 Jul 2019 16:39:26 GMT

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 ?

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

cfortin — Thu, 11 Jul 2019 16:40:06 GMT

It's working too !! thanks.

just need to add IL8 - before the mean.

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

Reeza — Thu, 11 Jul 2019 16:50:40 GMT

@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.

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

cfortin — Thu, 11 Jul 2019 16:51:57 GMT

Wonderful, thank you

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

PaigeMiller — Thu, 11 Jul 2019 17:02:18 GMT

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.

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

novinosrin — Thu, 11 Jul 2019 17:04:04 GMT

@PaigeMiller Very elegant indeed!

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

Reeza — Thu, 11 Jul 2019 17:06:59 GMT

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.

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

PaigeMiller — Thu, 11 Jul 2019 17:12:20 GMT

@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.

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

Reeza — Thu, 11 Jul 2019 17:15:13 GMT

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.

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

PaigeMiller — Thu, 11 Jul 2019 17:19:55 GMT

@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'.