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 |
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.