DATA Step, Macro, Functions and more

macro if then else

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

macro if then else

Hi,

 

I am trying to make a program that will calculate taxes based on brackets. Here are my brackets:

 

data tax;
input from1 1-6 to1 7-20  rate 21-24;
datalines;
0     18567         0.1
18568 34821         0.15
34822 65733         0.2
65734 9999999       0.25
;
run; 

I treid to do the following macro to calculate the taxes for each bracket:

 

%macro taxes (salary);
data tax_brackets;
set tax;

%if (&salary > to1) %then %do;
   tax_bracket = (to1 - from1)*rate;
   %end;
   %else %if (from1 <= &salary <= to1) %then %do  ; 
   tax_bracket = (&salary-from1)*rate;
 %end;
 
%mend;

%taxes (45000);

But only for the 3rd observation I get the intended result (which corresponds to the %else %if in the macro), while in all of the other observations I get a bad result.

 

Thanks! 


Accepted Solutions
Solution
‎06-03-2017 06:28 PM
Super User
Super User
Posts: 7,039

Re: macro if then else

[ Edited ]

I don't think that is how taxes work.  You only pay the higher rate on the part of the salaray that falls in the higher bracket.

You probably want something more like this. First let's create a tax table and some example employee data.

data tax_brackets;
  bracket+1;
  input from1 to1 rate ;
cards;
0     18567 0.10
18568 34821 0.15
34822 65733 0.20o
65734 .     0.25
;

data have;
  input id salary ;
cards;
1 10000
2 20000
3 40000
4 70000
;

Now let's combine the two tables. I will output both the detailed by bracket taxes and the overall per employee taxes.

data detail(drop=effective_rate rename=(tax=cummulative_tax))
     final(drop=partial_tax from1 to1 
           rename=(rate=marginal_rate bracket=marginal_bracket)
          ) 
;
  set have ;
  if 0 then set tax_brackets ;
  do p=1 to nobs until (salary < to1);
    set tax_brackets point=p nobs=nobs;
    partial_tax=rate*(min(salary,to1) - from1);
    tax = sum(0,tax,partial_tax);
    output detail;
  end;
  effective_rate = divide(tax,salary);
  output final;
run;

Capture.PNG

View solution in original post


All Replies
PROC Star
Posts: 733

Re: macro if then else

I dont see the need for a macro here. This seems simpler to me

 

data tax;
input from1 1-6 to1 7-20  rate 21-24;
datalines;
0     18567         0.1
18568 34821         0.15
34822 65733         0.2
65734 9999999       0.25
;

%let salary = 45000;
data tax_brackets;
   set tax;
   if &salary > to1 then tax_bracket = (to1 - from1)*rate;
   else if (from1 <= &salary <= to1) then tax_bracket = (&salary-from1)*rate;
run;
Super Contributor
Posts: 438

Re: macro if then else

It's not a good idea to mix macro and data step code in this way because they have different compilation and execution phases issues like this are almost inevitable. There are a number of different ways of doing this without a macro at all but if you have to use one this should work

 

data tax;
input from1 1-6 to1 7-20  rate 21-24;
datalines;
0     18567         0.1
18568 34821         0.15
34822 65733         0.2
65734 9999999       0.25
;
run; 

%macro taxes (salary);
	data tax_brackets;
	set tax;
	
	if (&salary > to1) then do;
   		tax_bracket = (to1 - from1)*rate;
	end;
	else if (from1 <= &salary <= to1) then do  ; 
   		tax_bracket = (&salary-from1)*rate;
	end;
 
%mend;

%taxes (45000);
Super User
Super User
Posts: 7,039

Re: macro if then else

[ Edited ]

 

You need to remember that macro code is used to GENERATE SAS code. It does not know what 'from1' or 'to1' are or even what a data step or proc step are. It is just generating code that SAS will then compile and run the same as if you had typed the code yourself.

 

If you turn on the MPRINT and MLOGIC options it is pretty easy to see what code the macro is generating and why.

So let's try it.

 

%macro taxes (salary);
data tax_brackets;
  set tax;
%if (&salary > to1) %then %do;
  tax_bracket = (to1 - from1)*rate;
%end;
%else %if (from1 <= &salary <= to1) %then %do  ; 
  tax_bracket = (&salary-from1)*rate;
%end;
run;
%mend;
options mprint mlogic;
%taxes (45000);

 

So it generates this code:

 

MLOGIC(TAXES):  Beginning execution.
MLOGIC(TAXES):  Parameter SALARY has value 45000
MPRINT(TAXES):   data tax_brackets;
MPRINT(TAXES):   set tax;
MLOGIC(TAXES):  %IF condition (&salary > to1) is FALSE
MLOGIC(TAXES):  %IF condition (from1 <= &salary <= to1) is TRUE
MPRINT(TAXES):   tax_bracket = (45000-from1)*rate;
MPRINT(TAXES):   run;

Note that macro comparisons will use %EVAL() which can only do integer numeric comparisons and then only when both expressions look like integers. The string 'from1' and 'to1' definitely do NOT look like intergers.

 

So '45000' comes before 'to1' since '4' comes before 't' so it will go down the ELSE clause.

Now why does (from1 <= &salary <= to1) evaluate to TRUE?  Because the macro processor does NOT split this into two comparisons like SAS would. So instead it evaluatiing the expression  ((from1 <= &salary) AND (&salary <= to1)) the macro processor will evaluate the binary operators from left to right.  So consider what happens if you evaluate the expression ((from1 <= &salary) <= to1).  The first part is false since 'f' comes after '4' and it will produce the string '0'. And '0' comes before 't' so the overall result is true.

 

If you want the data step to make different decisions for each observation then you need to use normal IF/THEN statements and not try to use macro code.

%macro taxes(salary);
data tax_brackets;
  set tax;
  if from1 <= &salary then do;
    tax_bracket = (min(to1,&salary) - from1)*rate;
  end;
run;
%mend;

If you did want to have the macro decide which of the two functions to use for every observation then you would need to give the macro the values of the upper and lower bounds that it needs to make the decision. 

 

Solution
‎06-03-2017 06:28 PM
Super User
Super User
Posts: 7,039

Re: macro if then else

[ Edited ]

I don't think that is how taxes work.  You only pay the higher rate on the part of the salaray that falls in the higher bracket.

You probably want something more like this. First let's create a tax table and some example employee data.

data tax_brackets;
  bracket+1;
  input from1 to1 rate ;
cards;
0     18567 0.10
18568 34821 0.15
34822 65733 0.20o
65734 .     0.25
;

data have;
  input id salary ;
cards;
1 10000
2 20000
3 40000
4 70000
;

Now let's combine the two tables. I will output both the detailed by bracket taxes and the overall per employee taxes.

data detail(drop=effective_rate rename=(tax=cummulative_tax))
     final(drop=partial_tax from1 to1 
           rename=(rate=marginal_rate bracket=marginal_bracket)
          ) 
;
  set have ;
  if 0 then set tax_brackets ;
  do p=1 to nobs until (salary < to1);
    set tax_brackets point=p nobs=nobs;
    partial_tax=rate*(min(salary,to1) - from1);
    tax = sum(0,tax,partial_tax);
    output detail;
  end;
  effective_rate = divide(tax,salary);
  output final;
run;

Capture.PNG

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 396 views
  • 6 likes
  • 4 in conversation