topic Re: Sum and multiplying with missing values in SAS Programming

Sum and multiplying with missing values

hovliza — Sun, 12 Nov 2017 15:13:36 GMT

Hi If I want to do the following:

DATA want;
SET have;
score=(intens*walk10*walk20) + (intens*jog10*jog20) + (intens*run10*run20) + (intens*bike10*bike20) + (intens*swim10*swim20) + (intens*racquet10*racquet20) + (intens*vig_ex10*vig_ex20) + (intens*ball10*ball20) + (intens*stren10*stren20);
RUN;

I don't get values for 'score'. I think it is because of the missing data. Do you know how I can handle the missing data here?

Re: Sum and multiplying with missing values

Shmuel — Sun, 12 Nov 2017 15:47:45 GMT

1) What do you want to do with a mising value ?

Do you want to ignore the multiplication (intent+xxx10*xxx20) for any xxx ?

2) Your formule is equivalent to:

intent * (walk10*walk20 + jog10*jog20 + run10*run20 ...)

You can use arrays and the function sum to get what you want:

data want;
  set have;
        array x10 walk10 jog10 run10 .... ;
        array x20 walk20 jog20 run20 .... ;
       score = 0;   /* initialize */
       do i=1 to dim(x10);
            score = sum(of score, x10(i) * x20(i));
       end;
       score = intens * score;
run;

Re: Sum and multiplying with missing values

hovliza — Sun, 12 Nov 2017 16:43:06 GMT

Thank you, I want SAS to not take the missing value in to account. So it only calculates with the non-missing values.

When I apply your syntax to my data, I only get 0 out of my score.

I don't understand what this means:

score = 0;   /* initialize */

And do I need to have another 'do' sentence? Like this;

do i=1 to dim(x20);

Re: Sum and multiplying with missing values

Shmuel — Sun, 12 Nov 2017 16:50:19 GMT

In my code I initialize the score with zero, then have a loop to calculate the sum of the multiplications.

finally multiply the total sum (in the score variable) by intens.

The statement: do i=1 to dim(x10); - assumes that for each x10 member there is a x20 member so

both arrays have same dimension.

Re: Sum and multiplying with missing values

Astounding — Sun, 12 Nov 2017 20:29:29 GMT

You haven't told us what it means to ignore missing values. For example, if only INTENS is missing, what should the result be? If only WALK10 is missing, what should the result be?

The programming isn't that difficult. But specifying the rules is.

Re: Sum and multiplying with missing values

hovliza — Mon, 13 Nov 2017 09:45:45 GMT

Sorry, this was the first time I used arrays. I read about it and understand the programming part. But I don't get values yet for score.. Is it because there are a lot of missing values? In all the parts of the formula there could be missing values. And I want SAS to only calculate with the non-missing values.

Re: Sum and multiplying with missing values

Shmuel — Mon, 13 Nov 2017 10:11:17 GMT

Run next code to count how many values are missing out of total values:

data _NULL_;
  set have  end=eof ;
        array x10 walk10 jog10 run10 .... ;
        array x20 walk20 jog20 run20 .... ;
        
       retain total  missvalue 0;
       do i=1 to dim(x10);
            total + 2;   /* two variables to check */
            if x10(i) = . then missvalue+1;
            if x20(i) = . then missvalue+1;
       end; 

       if eof then put total= missvalue=;
run;

Check message in log.

Re: Sum and multiplying with missing values

hovliza — Mon, 13 Nov 2017 10:35:19 GMT

Yes, I did that: total=2088 missvalue=1634. So a lot of missing values. But is there a way to do this datastep with only the non-missing values? Because some values in 'intens' variable are also missing. So;

score=intens*score;

will give a lot of zero's..

Is there a way to deal with that? I tried this;

IF intens>. THEN DO;
score=intens*score;

But doesn't help..

Re: Sum and multiplying with missing values

Shmuel — Mon, 13 Nov 2017 11:10:49 GMT

You should remeber that any calculation with missing value will result into missing value,

except when using some functions or procedures:

let assign miss = .; as missing value then

any_X + miss = miss (same with subtraction)

any_X * miss = miss

Using function (as I did in code proposed) SUM(OF ...) treats missing value as zero,

then sum(of any_X , miss) = any_X.

In case that INTENS is missing then you can skip those rows:

data want;
  set have (where=(intens ne .));    /* WHERE condition added */
        array x10 walk10 jog10 run10 .... ;
        array x20 walk20 jog20 run20 .... ;
       score = 0;   /* initialize */
       do i=1 to dim(x10);
            score = sum(of score, x10(i) * x20(i));
       end;       
      score = intens * score;
run;

Re: Sum and multiplying with missing values

hovliza — Mon, 13 Nov 2017 12:43:04 GMT

Thanks for your help! I think I found the solution! 🙂