BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hovliza
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

9 REPLIES 9
Shmuel
Garnet | Level 18

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
Obsidian | Level 7

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
Garnet | Level 18

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
Obsidian | Level 7

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
Garnet | Level 18

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
Obsidian | Level 7

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
Garnet | Level 18

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
Obsidian | Level 7

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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