topic Missing values in addition or substraction generate error in SAS Procedures

Missing values in addition or substraction generate error

nicnad — Mon, 02 Feb 2015 22:44:23 GMT

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!

Re: Missing values in addition or substraction generate error

ballardw — Mon, 02 Feb 2015 23:14:07 GMT

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.

Re: Missing values in addition or substraction generate error

Reeza — Mon, 02 Feb 2015 23:30:49 GMT

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