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
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.
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
@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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.