topic Re: calculating average for derive variables in SAS Programming

calculating average for derive variables

Emma_at_SAS — Mon, 01 Mar 2021 05:41:09 GMT

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

Re: calculating average for derive variables

andreas_lds — Mon, 01 Mar 2021 06:21:28 GMT

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
;

Re: calculating average for derive variables

Emma_at_SAS — Mon, 01 Mar 2021 16:53:11 GMT

Thank you, andreas! Great and easy solution!

Re: calculating average for derive variables

Reeza — Mon, 01 Mar 2021 17:01:03 GMT

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.

Re: calculating average for derive variables

Emma_at_SAS — Mon, 01 Mar 2021 17:03:53 GMT

Thanks, Reeza. Good to know.