BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

  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!

2 REPLIES 2
ballardw
Super User

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.

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 953 views
  • 0 likes
  • 3 in conversation