Sum and multiplying with missing values

[hovliza]

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?

[Shmuel]

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;

[hovliza]

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); 

[Shmuel]

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.

[hovliza]

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. 

[Shmuel]

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.

[hovliza]

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

[Shmuel · Accepted solution]

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;

[hovliza]

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

[Astounding]

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.