02-02-2015 05:44 PM
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;
create table work.test as select
(mytable.var1 + mytable.var2 - mytable.var3) as my_calculation
from mylib.mytable as mytable
Thank you for your help and time!
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
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.
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