topic Re: How to sum IF the variables exist in SAS Procedures

How to sum IF the variables exist

Mahtalii — Thu, 19 Apr 2018 22:15:57 GMT

Hello All,

I want to do a simple sum() function over some variables in my tables. And since I have so many tables, I want to write one macro code for all of them. The problem is, not all the tables have all the variables! And if you run sum(a1, a2, a3) while for example, "a1" doesn't exist in the table, you'll get an error!

I wonder if there is any way to tell SAS to sum(a1, a2, a3) for those that exist in the table.

For example, if "a1" doesn't exist, then sum(a1, a2, a3) should give me sum(a2, a3). And if none of them exist then missing value.

Any suggestion is much appreciated.

Cheers,

Re: How to sum IF the variables exist

ballardw — Thu, 19 Apr 2018 22:44:44 GMT

@Mahtalii wrote:

Hello All,

I want to do a simple sum() function over some variables in my tables. And since I have so many tables, I want to write one macro code for all of them. The problem is, not all the tables have all the variables! And if you run sum(a1, a2, a3) while for example, "a1" doesn't exist in the table, you'll get an error!

You do not get an error in a data step. If you use sum or addition with a variable that is not previously defined you will get a NOTE in the log that the variable is uninitialized and add a variable of that name to the output data set if any with a missing value but not an error. Of course then you have added an undocumented variable or more to a data set.

So I have to assume you are attempting this in Proc SQL or possibly elsewhere.

You might show the macro you are currently attempting and an example of use to see if a modification is possible to the existing code. The general approach would require accessing the dataset to check the existence of the variables in the set, determine if it is numeric in the set and then return a reduced set of variables to be referenced in an (assumed) SUM function call.

Re: How to sum IF the variables exist

Reeza — Thu, 19 Apr 2018 23:41:46 GMT

Do you have a naming convention to your variables? If so, like in your sample, you can use variable shortcuts.

Sum(of a:);

Sum(of a1-a3);

@Mahtalii wrote:

Hello All,

I want to do a simple sum() function over some variables in my tables. And since I have so many tables, I want to write one macro code for all of them. The problem is, not all the tables have all the variables! And if you run sum(a1, a2, a3) while for example, "a1" doesn't exist in the table, you'll get an error!

I wonder if there is any way to tell SAS to sum(a1, a2, a3) for those that exist in the table.

For example, if "a1" doesn't exist, then sum(a1, a2, a3) should give me sum(a2, a3). And if none of them exist then missing value.

Any suggestion is much appreciated.

Cheers,