topic Re: Dropping a group while taking the mean in SAS Programming

Dropping a group while taking the mean

RandyStan — Fri, 07 Oct 2022 18:10:17 GMT

Dear All:

My SAS Data is as follows

Date Group Var_A

02OCT2020 0 22

02OCT2020 1 28

02OCT2020 2 33

03OCT2020 0 55

03OCT2020 1 75

03OCT2020 2 86

I want to take the mean of VAR_A by excluding Group 0 from the calculation. There is a long method of doing this by creating a new data set excluding 0. Is there a shorter way to do this so that I can preserve the data set.

Thanks much

Re: Dropping a group while taking the mean

ballardw — Fri, 07 Oct 2022 19:00:18 GMT

One way to get the mean, or other statistics, would be:

Proc Means data=have mean;
   where group ne 0;
   var var_a;
run;

IF you want a data set you should provide what you want as a result as there are a number of ways to do this but specifics may change based on desired content.

Re: Dropping a group while taking the mean

Astounding — Fri, 07 Oct 2022 19:01:29 GMT

Getting the means is easy. PROC steps support the WHERE statement:

proc means data=have;
var var_a;
where group ne 0;
run;

You still have to decide whether you want a separate mean for each date, and whether you want to merge the results back into the original data.

Re: Dropping a group while taking the mean

Reeza — Fri, 07 Oct 2022 20:26:46 GMT

Making huge assumptions that you're doing it by date and want to add the mean to the data set.

proc sql;
create table summary as
select date, group, var_a, sum(case when group ne 0 then var_a else 0 end)/sum(case when group ne 0 then 1 else 0 end) as avg_minus_group0
from have
group by date
order by 1, 2, 3;
quit;

@RandyStan wrote:

Dear All:

My SAS Data is as follows

Date Group Var_A

02OCT2020 0 22

02OCT2020 1 28

02OCT2020 2 33

03OCT2020 0 55

03OCT2020 1 75

03OCT2020 2 86

I want to take the mean of VAR_A by excluding Group 0 from the calculation. There is a long method of doing this by creating a new data set excluding 0. Is there a shorter way to do this so that I can preserve the data set.

Thanks much

Re: Dropping a group while taking the mean

PaigeMiller — Fri, 07 Oct 2022 21:39:58 GMT

@Reeza wrote:

Making huge assumptions that you're doing it by date and want to add the mean to the data set.
proc sql;
create table summary as
select date, group, var_a, sum(case when group ne 0 then var_a else 0 end)/sum(case when group ne 0 then 1 else 0 end) as avg_minus_group0
from have
group by date
order by 1, 2, 3;
quit;

This does not give a correct answer if one or more values of var_a is missing. PROC MEANS, on the other hand, gives the correct answer if one or more values of var_a is missing.

Re: Dropping a group while taking the mean

Reeza — Fri, 07 Oct 2022 21:45:48 GMT

Nope, it won't. But OP hasn't explained their requirements well enough. Proc Means is multiple steps and I have the impression they'd like to avoid that....but it's definitely a guess.

Re: Dropping a group while taking the mean

Tom — Sat, 08 Oct 2022 02:22:02 GMT

PROC SQL knows how to take a MEAN also.

select date
     , group
     , var_a
     , mean(case when group ne 0 then var_a else . end) as avg_minus_group0
from have
group by date