weight factor algorithm

[chuie]

Hi

I  have a data set with employee ID, performance rating,placement relative to market rate and their current salary.

So I need to calculate the % merit increase based off which category do they fall under ( blue, green, orange or red) zone. Also the important factor here  is that  total % merit should not exceed to x budget

Budget =25000

So  each employee ( n = 12) fall under blue (5) or green(6) or orange(1) or red(0).

We have to calculate the merit increase of blue(?), green(?), orange(?) and red(?) in a way that total merit increase should not exceed the budget of 25000.Obiously the merit increase should be more for blue as they  are high performer but their current salary falls in lower percentile and reds are lower performer but their current salary is higher percentile and so on.

Any guidance would be highly appreciated.

Thanks

C

[Community_Guide]

Hello @chuie,

Your question requires more details before experts can help. Can you revise your question to include more information? 

Review this checklist:

• Specify a meaningful subject line for your topic.  Avoid generic subjects like "need help," "SAS query," or "urgent."
• When appropriate, provide sample data in text or DATA step format.  See this article for one method you can use.
• If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
  
• It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message.  From there you can adjust the title and add more details to the body of the message.  Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @Reeza

.

[Reeza]

If you can post data as text not images that's helpful, otherwise we have to type it out to work with it. 

Additionally, you need to provide more details of the logic you're trying to apply and a small example with the matching output would help a lot. 

[chuie]

Thank you Reeza for  willing to guide.

Here is the sample data ,

data merit;
input id rating $ rate $ salary ;
datalines;
1 A_Exceed A_min 18000
2 B_Meetex B_low 15000
3 C_meetba C_med 19000
4 D_Basic  D_hig 22000
5 A_Exceed A_min 18000
6 B_Meetex D_hig 12000
7 A_Exceed A_min 14000
8 B_Meetex D_hig 18000
;
run;
proc freq data=merit;
tables rating*rate/nopercent norow nocol;
run;

So we have a departmental budget allocated for merit increase  lets say 25000

Annually we give salary increase (%)  to all employee based on their performance (  here is rating) and salary category ( rate) .

 Clause # 1 : The total merit salary increase  for all 8 employee should not exceed more than 25000 budget.

Clause #2 : We have to create an algorithm where employee with high performer ( rating = A_ exceed, B_Meetex)  AND also their current salary ( rate = A_min, B_low) should get higher merit percentage while employee who are performing lower ( rating = C_meetba or D_basic) and aslo their  current rate ( = D_high, C_med)  gets lower merit increase.

here ID 1, 5 and 7 gets higher  % of merit increase  and  ID 4 gets lowest % merit increase.

(a%*18000)+(b%*15000) + (c%*19000) + (d%*22000) + (e%*18000) + (f%*12000) +(g%*14000)+(h%100*18000) <25000

    ID =1           ID=2                 ID=3                ID=4              ID=5             ID=6               ID=7            ID=8

here we have to calculate a% b% c% d% e% f% g% h% =?

 also

[Reeza]

What about a high performer being paid under market rate?

I would consider doing the % last. You have 25000, first figure out the ranking of employee's and how you want to convert that to percentages. I'm not really seeing the logic there to do that, just some generalities on X should get more and Y should get less but no definitions on how. If X gets a 10% raise and Y gets a 1% raise how do we know if this is ok. It does mean that this problem does not have a unique solution though.

[chuie]

  To simplify there are  employee who falls under for category ( blue, green, orange and red)

Can we give weight factor to each category  in a way that  equation does not exceed to the budget . I do not know how but there must be something like adjusted weighting factor.

[chuie]

Reeza,

I  came across a goal seeking function in excel and trying to do similar in sas using proc model .Let see if this  lead me something close.

Reference : http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/viewer.htm#etsug_model_sect066....

Goal Seeking: Solving for Right-Hand-Side Variables

The process of computing input values that are needed to produce target results is often called goal seeking. To compute a goal-seeking solution, use a SOLVE statement that lists the variables you want to solve for and provide a data set that contains values for the remaining variables.

Consider the following demand model for packaged rice

where price is the price of the package and income is disposable personal income. The only variable the company has control over is the price it charges for rice. This model is estimated by using the following simulated data and PROC MODEL statements:

   data demand;
      do t=1 to 40;
         price = (rannor(10) +5) * 10;
         income = 8000 * t ** (1/8);
         demand = 7200 - 1054 * price ** (2/3) +
                  7 * income + 100 * rannor(1);
         output;
      end;
   run;
   
   data goal;
      demand = 85000;
      income = 12686;
   run;

The goal is to find the price the company would have to charge to meet a sales target of 85,000 units. To do this, a data set is created with a DEMAND variable set to 85000 and with an INCOME variable set to 12686, the last income value.

The desired price is then determined by using the following PROC MODEL statements:

   proc model data=demand
              outmodel=demandModel;
      demand = a1 - a2 * price ** (2/3) + a3 * income;
      fit demand / outest=demest;
      solve price / estdata=demest data=goal solveprint;
   run;

The SOLVEPRINT option prints the solution values, number of iterations, and final residuals at each observation. The SOLVEPRINT output from this solve is shown in Figure 18.80.

Figure 18.80 Goal Seeking, SOLVEPRINT Output

The MODEL Procedure

Single-Equation Simulation

ObservationIterationsCCERROR.demand

1

6

0.000000

0.000000

Solution Valuesprice

33.59016

The output indicates that it took six Newton iterations to determine the PRICE of 33.5902, which makes the DEMAND value within 16E–11 of the goal of 85,000 units.

Consider a more ambitious goal of 100,000 units. The output shown in Figure 18.81 indicates that the sales target of 100,000 units is not attainable according to this model.

   data goal;
      demand = 100000;
      income = 12686;
   run;
   
   proc model model=demandModel;
      solve price / estdata=demest data=goal solveprint;
   run;