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
- /
- SAS Procedures
- /
- Missing values in addition or substraction generat...

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-02-2015 05:44 PM

Hi,

I have a table (mylib) that contains 3 variables and some have missing values that. They are automatically replaced by a dot in my SAS table.

I would like to make a simple arithmetic equation, but the result is always 0. Can you please help me with the following proc sql;

proc sql;

create table work.test as select

(mytable.var1 + mytable.var2 - mytable.var3) as my_calculation

from mylib.mytable as mytable

;quit;

Thank you for your help and time!

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

02-02-2015 06:14 PM

With a single table you're being a bit redundant mentioning the dataset name everywhere but no syntax issue.

Some values for your input might help.

HOWEVER you need to know that missing + value = missing. So if any of the values are missing the way you have written you are, in effect getting a Boolean of False. If it was summing 3 variable then the SUM function would be sufficient as that is what it is designed for.

Because you are incorporating a subtraction there isn't an elegant solution but you might use a case statement similar to

case

when missing(var3) then sum(var1,var2)

else (sum(var1,var2) - var3)

end as my_calculation

But we need additional information about what kind of result you expect with var1 and var2 are missing and var3 is not missing.

Some example input data and expected output may be helpful.

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

02-02-2015 06:30 PM

You can use the sum function if you can assume that missing values can be ignored.

Otherwise you'll need to deal with them some other way, depends on the data.

sum(mytable.var1, mytable.var2, -1*mytable.var3) as my_calculation