BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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;
ChrisBrooks
Ammonite | Level 13

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);
Tom
Super User Tom
Super User

 

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. 

 

Tom
Super User Tom
Super User

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 2720 views
  • 6 likes
  • 4 in conversation