DATA Step, Macro, Functions and more

Handling missing values in macro while performing arithmetic operations.

Reply
Occasional Contributor
Posts: 15

Handling missing values in macro while performing arithmetic operations.

Hi All,

 

I have missing values in one of the macro variable. While performing arithmetic operation the result shows missing value. Is there any way to handle the missing macro value. For Example in Subtract macro value there is one missing value.

I want the Subtract to show 

Subtract= . - 10 - 20  i.e.   -10

 

%let a=.;

%let b=10;

%let c=20;

 

data _null_;

    %let Add=%sysevalf(&a+&b+&c);

   %let Subtract=%sysevalf(&a-&b-&c);

   %let Div=%sysevalf(&a/&b);

run;

    

%put &Add is .

%put &Subtract is .

%put &Div is .

 

 

 

Super User
Posts: 6,939

Re: Handling missing values in macro while performing arithmetic operations.

1. A mathematical operation invovlving missing values will always yield missing values. In Base SAS and in the macro engine.

 

2. Your math is skewed. 0 - 10 - 20 = -30 and not -10.

 

3. You will be better off doing calculations in a data _null_ step and setting macro variables with call symput. This allows the easy use of functions that are robust with regard to missing values (eg the sum() function).

 

4. Your construct:

data _null_;
    %let Add=%sysevalf(&a+&b+&c);
   %let Subtract=%sysevalf(&a-&b-&c);
   %let Div=%sysevalf(&a/&b);
run;

is bogus. This creates an empty data _null_ step that does nothing (besides annoying the SAS interpreter); the macro statements are dealt with by the macro engine before the data step is compiled and executed. Macro statements do not need data or proc steps around them. They might need a macro definition (%if and %do, which are not allowed in open code).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 490

Re: Handling missing values in macro while performing arithmetic operations.

[ Edited ]

If you want to use %sysevalf, then use only nonmissing values within the first argument to the %SYSEVAL function.

 

As one work around make sure your macro variables is not equal to missing values like 

 

%let a=%sysfunc(sum(.,0));
%let b=%sysfunc(sum(10,0));
%let c=%sysfunc(sum(20,0));
 

 By that any missing values will be equal to zero.

Super User
Super User
Posts: 7,401

Re: Handling missing values in macro while performing arithmetic operations.

No, the real question here is why on earth are you doing numerical imputations in a textual code generation system?  I mean do you use Word as a calculator?  Let me explain:

Macro language is a technique of generating text, it has some conditionals and what not, but it not itself compilable code, it is merely there to help generate compilable code to save you having to type things.  Base SAS is the compilable code, it is there you would do all your computations, data manipulations etc.  If for some unkown reason you do need a result in a macro variable, then @KurtBremser has provided you with an answer, use Base SAS data _null_ and call symput to generate the macro variable.

Ask a Question
Discussion stats
  • 3 replies
  • 239 views
  • 1 like
  • 4 in conversation