BookmarkSubscribeRSS Feed
ali_far
Obsidian | Level 7

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;

5 REPLIES 5
Reeza
Super User

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;


 

 

ali_far
Obsidian | Level 7
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
Reeza
Super User

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

 

 

ali_far
Obsidian | Level 7

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

 

ali_far
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1108 views
  • 0 likes
  • 2 in conversation