Dropping a group while taking the mean

[RandyStan]

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

[ballardw]

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.

[Astounding · Accepted solution]

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.

[Reeza]

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

---

[PaigeMiller]

---

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

[Reeza]

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.

[Tom]

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