calculating average for derive variables

[Emma_at_SAS]

Hello,

I want to create a column (column_3) based on two other columns (column_1 and column_2).

• if both column_1 and column_2 have a value then column_3 is the average of column_1 and column_2
• If the values for either column_1 or column_2 are missing (only one of them has a value) then column_3 is set equal to the non-missing value.

I know my code is wrong, but something like this

data want;
set have;
column_3=.;
if column_1 eq '.' and column_2>0 then column_3=column_2;
if column_2 eq '.' and column_1 >0 then column_3=column_1;
else if column_1>0 and column_2>0 then column_3 = (column_1+column_2)/2;

run;

Thanks

[andreas_lds · Accepted solution]

Assuming that the variables are numeric, you just compare with . not enclosed in quotes. But in your task, you don't have to use if at all. The function mean takes care of missing values:

data havewant;
   input col1 col2;
   
   col3 = mean(col1, col2);
   
   datalines;   
5 .
. 7
4 6
;

[Emma_at_SAS]

Thank you, andreas! Great and easy solution!

[Reeza]

The MEAN() function does that automatically so you don't need to do any manual checks.
If dealing with missing values, another function that's often used is COALESCE(), which takes the first non missing values from a list of variables.

[Emma_at_SAS]

Thanks, Reeza. Good to know.