DATA Step, Macro, Functions and more

Sum and multiplying with missing values

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Sum and multiplying with missing values

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?


Accepted Solutions
Solution
‎11-13-2017 07:43 AM
Trusted Advisor
Posts: 1,826

Re: Sum and multiplying with missing values

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;

          

View solution in original post


All Replies
Trusted Advisor
Posts: 1,826

Re: Sum and multiplying with missing values

[ Edited ]

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;

 

Contributor
Posts: 32

Re: Sum and multiplying with missing values

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); 
Trusted Advisor
Posts: 1,826

Re: Sum and multiplying with missing values

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.

 

Contributor
Posts: 32

Re: Sum and multiplying with missing values

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. 

Trusted Advisor
Posts: 1,826

Re: Sum and multiplying with missing values

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.

Contributor
Posts: 32

Re: Sum and multiplying with missing values

[ Edited ]

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

Solution
‎11-13-2017 07:43 AM
Trusted Advisor
Posts: 1,826

Re: Sum and multiplying with missing values

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;

          

Contributor
Posts: 32

Re: Sum and multiplying with missing values

Thanks for your help! I think I found the solution! Smiley Happy

Super User
Posts: 6,543

Re: Sum and multiplying with missing values

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 599 views
  • 0 likes
  • 3 in conversation