Help using Base SAS procedures

Missing values in addition or substraction generate error

Reply
Regular Contributor
Posts: 186

Missing values in addition or substraction generate error

  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!

Super User
Posts: 10,466

Re: Missing values in addition or substraction generate error

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.

Super User
Posts: 17,750

Re: Missing values in addition or substraction generate error

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

Ask a Question
Discussion stats
  • 2 replies
  • 189 views
  • 0 likes
  • 3 in conversation