Contributor
Posts: 20

# getting average in a column

Hi all,

obs  Var1      Var2    Var3

1       1           0         20

2       1           0         10

3       1           1          20

4       2            0         20

5       2            0         10

6       2            1         30

.       .

.       .

data want;

set  have;

if var1=lag(var1) then do;

if Var2=1 then want=var3;

if var2=0 then want=mean(var3); /*this line did not perform*/

end;run;

Super User
Posts: 23,296

## Re: getting average in a column

The MEAN() function works on a single row of data, so the mean of a value is the same value.

Rather than use var1=lag(var1) I suggest reviewing how BY group processing works and letting SAS handle the grouping transitions for you instead of coding it manually. It's a very powerful feature.

It's not clear exactly what you want, if you want more than an explanation of why it isn't working, please show what you are expecting as output from the input and the logic required.

@ali_far wrote:

Hi all,

obs  Var1      Var2    Var3

1       1           0         20

2       1           0         10

3       1           1          20

4       2            0         20

5       2            0         10

6       2            1         30

.       .

.       .

data want;

set  have;

if var1=lag(var1) then do;

if Var2=1 then want=var3;

if var2=0 then want=mean(var3); /*this line did not perform*/

end;run;

Contributor
Posts: 20

## Re: getting average in a column

Based on the data set i think instead of using proc sql, grouping, I use this code.
I want to create a variable by some condition. first group observations with the same var1. then if var2 is 1 the 'want var' is the same as var 3. if var 2 is 0 'want var' is the average of var 3.
for obs 1-3:
obs var1 var2 var 3 want
1 1 0 20 15 ((20+10)/2)
2 1 0 10 15
3 1 1 20 20
Super User
Posts: 23,296

## Re: getting average in a column

[ Edited ]

A SQL approach will likely be the easiest, possibly a single step solution. A data step solution will be more than one step.

1. Add the average value to each row so it's available, easily done in SQL

2. Add a case statement to do the assignment.

``````proc sql;
create table want (drop=avg_Var) as
select *,
mean(var3) as avg_var,
case when var2=0 then calculated avg_var
else var3
end as new_var
from have
group by var1;
quit;``````

@ali_far wrote:
Based on the data set i think instead of using proc sql, grouping, I use this code.
I want to create a variable by some condition. first group observations with the same var1. then if var2 is 1 the 'want var' is the same as var 3. if var 2 is 0 'want var' is the average of var 3.
for obs 1-3:
obs var1 var2 var 3 want
1 1 0 20 15 ((20+10)/2)
2 1 0 10 15
3 1 1 20 20

Contributor
Posts: 20

## Re: getting average in a column

Thanks  for your reply. However, the code calculates the mean of var3 where var2 in (1,2).

However, I want the mean of var3 where var2=0  and else var3..

Contributor
Posts: 20