topic Re: getting average in a column in SAS Programming

getting average in a column

ali_far — Sun, 03 Jun 2018 03:19:09 GMT

Hi all,

I have a question about this code. it is about getting average in a column.

your help is appreciated.

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;

Re: getting average in a column

Reeza — Sun, 03 Jun 2018 03:23:40 GMT

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,

I have a question about this code. it is about getting average in a column.

your help is appreciated.

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;

Re: getting average in a column

ali_far — Sun, 03 Jun 2018 03:40:47 GMT

Thanks for your reply.
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

Re: getting average in a column

ali_far — Sun, 03 Jun 2018 03:58:02 GMT

Re: getting average in a column

Reeza — Sun, 03 Jun 2018 04:13:37 GMT

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

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

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:
Thanks for your reply.
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

Re: getting average in a column

ali_far — Sun, 03 Jun 2018 05:07:20 GMT

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

I appreciate your help