Using SAS to add Risk Adjustments based on Start Date to Earnings

[Singham20]

Hi!

I am having trouble with applying risk adjustments to my various projects. What I have is 15 Projects that start in the beginning of quarters over the next 3 years. These projects all have a potential earning associated with them. The potential earnings do not change for a project once it starts. For instance Project 1 starts on 1/1/2020 and has a potential earning of 2. This means starting 1/1/2020 and through the end of the timeframe in the data table the earning of Project 1 will be 2 for every month. So for Project 7, which starts on 7/1/2020 and has a potential earning of 1, it will have no earnings from 1/1/2020 to 6/1/2020; however on 7/1/2020 and onwards it will have a potential earning of 1. From here is where the confusion arises for me. We expect that there is a good chance that the potential earnings will not be met so we need to account for that. Regardless of the start date the risk adjustment is as follows:

For first 3 months we will only see 30%. For the next 3 months we will see 60%. For 6+ months since start date we should see 80% of the potential.

1-3 months from start date: (30% * Potential Earnings)

4-6 months from start date: (60% * Potential Earnings)

7+ months from start date: (80% * Potential Earnings)

So for Project 1 which as an earnings potential of 2 and starts in 1/1/2020 I should see the following:

1/1/2020 (2*30%)

2/1/2020 (2*30%)

3/1/2020 (2*30%)

4/1/2020 (2*60%)

5/1/2020 (2*60%)

6/1/2020 (2*60%)

7/1/2020 (2*80%)

9/1/2020 (2*80%)

And so on …

After we get these risk adjustments to all the projects depending on the start data I need to sum it for each month of the year and group the total by the Group variable, which is A or B. Below is a table to see what I am talking about. Hope someone can help me on this SAS Homework!

Group	A or B	A	B	A	A	B	A	B	B	B	B	B	A	A	A	B
Project	Number	Project 1	Project 2	Project 3	Project 4	Project 5	Project 6	Project 7	Project 8	Project 9	Project 10	Project 11	Project 12	Project 13	Project 14	Project 15
Project Start	Date	1/1/2020	1/1/2020	1/1/2020	1/1/2020	1/1/2020	7/1/2020	7/1/2020	7/1/2020	10/1/2020	10/1/2020	4/1/2021	4/1/2021	1/1/2022	1/1/2022	10/1/2022
Expected	Earnings	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
Date	Quarter
1/1/2020	Q1	2	4	2	1	3
2/1/2020	Q1	2	4	2	1	3
3/1/2020	Q1	2	4	2	1	3
4/1/2020	Q2	2	4	2	1	3
5/1/2020	Q2	2	4	2	1	3
6/1/2020	Q2	2	4	2	1	3
7/1/2020	Q3	2	4	2	1	3	22	1	8
8/1/2020	Q3	2	4	2	1	3	22	1	8
9/1/2020	Q3	2	4	2	1	3	22	1	8
10/1/2020	Q4	2	4	2	1	3	22	1	8	4	2
11/1/2020	Q4	2	4	2	1	3	22	1	8	4	2
12/1/2020	Q4	2	4	2	1	3	22	1	8	4	2
1/1/2021	Q1	2	4	2	1	3	22	1	8	4	2
2/1/2021	Q1	2	4	2	1	3	22	1	8	4	2
3/1/2021	Q1	2	4	2	1	3	22	1	8	4	2
4/1/2021	Q2	2	4	2	1	3	22	1	8	4	2	5	1
5/1/2021	Q2	2	4	2	1	3	22	1	8	4	2	5	1
6/1/2021	Q2	2	4	2	1	3	22	1	8	4	2	5	1
7/1/2021	Q3	2	4	2	1	3	22	1	8	4	2	5	1
8/1/2021	Q3	2	4	2	1	3	22	1	8	4	2	5	1
9/1/2021	Q3	2	4	2	1	3	22	1	8	4	2	5	1
10/1/2021	Q4	2	4	2	1	3	22	1	8	4	2	5	1
11/1/2021	Q4	2	4	2	1	3	22	1	8	4	2	5	1
12/1/2021	Q4	2	4	2	1	3	22	1	8	4	2	5	1
1/1/2022	Q1	2	4	2	1	3	22	1	8	4	2	5	1	2	3
2/1/2022	Q1	2	4	2	1	3	22	1	8	4	2	5	1	2	3
3/1/2022	Q1	2	4	2	1	3	22	1	8	4	2	5	1	2	3
4/1/2022	Q2	2	4	2	1	3	22	1	8	4	2	5	1	2	3
5/1/2022	Q2	2	4	2	1	3	22	1	8	4	2	5	1	2	3
6/1/2022	Q2	2	4	2	1	3	22	1	8	4	2	5	1	2	3
7/1/2022	Q3	2	4	2	1	3	22	1	8	4	2	5	1	2	3
8/1/2022	Q3	2	4	2	1	3	22	1	8	4	2	5	1	2	3
9/1/2022	Q3	2	4	2	1	3	22	1	8	4	2	5	1	2	3
10/1/2022	Q4	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
11/1/2022	Q4	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
12/1/2022	Q4	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
1/1/2023	Q1	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
2/1/2023	Q1	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
3/1/2023	Q1	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
4/1/2023	Q2	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
5/1/2023	Q2	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
6/1/2023	Q2	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
7/1/2023	Q3	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
8/1/2023	Q3	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
9/1/2023	Q3	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
10/1/2023	Q4	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
11/1/2023	Q4	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4
12/1/2023	Q4	2	4	2	1	3	22	1	8	4	2	5	1	2	3	4

[esjackso]

You post the goal but dont actually post what you have tried or where you have an issue in your code. Hard to help with your issue if you dont say what the issue is you have.

Eric

[Patrick]

@Singham20 

First step is to read your source data into a SAS table in a long structure. Below a SAS data step which creates some sample data in such a long structure.

data have_long(drop=_i);
  attrib
    group project length=$8.
    start_dt expected_dt format=ddmmyy10.
    expected_earning format=best32.
    ;
  group='A';
  project='P1';
  start_dt='01jan2020'd;
  expected_earning=2;

  do _i=0 to 47;
    expected_dt=intnx('month',start_dt,_i,'b');
    output;
  end;

  group='B';
  project='P8';
  start_dt='01jul2020'd;
  expected_earning=8;

  do _i=0 to 40;
    expected_dt=intnx('month',start_dt,_i,'b');
    output;
  end;
run;

Once you've got your data organized just compare the start date with the current date in the row and then use the adjustment factor based on the month you're in.

data risk_adjusted;
  set have_long;
  attrib 
    expected_earning_adj format=best32.
    ;
  /* month 7+ */
  if intck('month',start_dt,expected_dt)>=6 then
    expected_earning_adj=expected_earning*0.8;
  /* 4th to 6th month */
  else if intck('month',start_dt,expected_dt) in (3,4,5) then
    expected_earning_adj=expected_earning*0.6;
  /* first 3 months */
  else if intck('month',start_dt,expected_dt) in (0,1,2) then
    expected_earning_adj=expected_earning*0.3;
run;

proc print data=risk_adjusted;
run;

In case it's not clear to you what functions intck() and intnx() are doing, start with reading the docu here.

To create the report: Look into Proc Report.