turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Sum and multiplying with missing values

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-12-2017 10:13 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to hovliza

11-13-2017 06:10 AM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to hovliza

11-12-2017 10:46 AM - edited 11-12-2017 10:47 AM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

11-12-2017 11:43 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to hovliza

11-12-2017 11:50 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

11-13-2017 04:45 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to hovliza

11-13-2017 05:11 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

11-13-2017 05:30 AM - edited 11-13-2017 05:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to hovliza

11-13-2017 06:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

11-13-2017 07:43 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to hovliza

11-12-2017 03:29 PM

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.