Hi Folks,
I'm struggling to write a program for trend analysis, below is screenshot of what I am trying to accomplish
I have Adjusted_PMPM value for three years (2015,2016, 2017). I have to find the Expected 1 and Expected 2 column values using the formula: Adjusted_PMPM*(1+R), Where, R is 7%.
Value of R is constant for the entire calculation, it's the Adjusted_PMPM which changes.
For Expected_1, start from 2015:
For Expected_1 value for 2016, it will be : 3304.55*(1+0.07) = 3545.55
but then Expected_1 value for 2017 will be 3545.55*(1+0.07) = 3804.12
Then again for Expected_2 value, we will start from 2016
So Expected_2 value for 2017 will be 3145.41*(1+0.07) = 3374.80
And finally, Average the two expected values for 2017 to get the final value in the last column.
I'm attaching the Excel sheet, if in case that helps in the process.
I tried adding the SAS dataset, it won't let me add the dataset here.
Any help will be much appreciated.
Yes, indeed your program is more complicated than needed. In particular, you should take advantage of two features of the DATA step:
As a first effort, don't worry about carryover from prior product_types. Just see if you can use the LAG function to generate the 2 new variables EXP1 and EXP2. Once you've got that, then return to the task of setting EXP1 to missing for the first record of each type, and first 2 recs for EXP2.
Here's the starting data set, all sasified:
data have;
input PROD_TYPE $4.Calendar_year ADJUSTED_PMPM :dollar9.2 Trend percent3.;
datalines;
Advo 2015 $3,304.55 7%
Advo 2016 $3,145.41 7%
Advo 2017 $3,229.17 7%
GRP 2015 $2,956.19 1%
GRP 2016 $3,238.21 1%
GRP 2017 $4,184.82 1%
IFP 2015 $4,706.53 9%
IFP 2016 $4,634.87 9%
IFP 2017 $5,308.22 9%
run;
I have done it this far.
If someone can help me improvise this, I'll be grateful.
Data test (keep=Prod_type Calendar_year Adjusted_PMPM Trend Expected_1 Expected_2);
obs1 = 1;
do while (obs1 < = nobs);
set Non_engaged NOBS=nobs;
by prod_type;
obs2= obs1-1;
set Non_engaged (rename=(Prod_type = Prod_type_1
Calendar_year = Calendar_year_1
adjusted_PMPM = adjusted_PMPM_1
trend = trend_1)) point=obs2;
Expected_1 = adjusted_PMPM_1*(1+trend);
obs3= obs2-1;
set Non_engaged (rename=(Prod_type = Prod_type_2
Calendar_year = Calendar_year_2
adjusted_PMPM = adjusted_PMPM_2
trend = trend_2)) point=obs3;
Expected_1x = adjusted_PMPM_2*(1+trend);
Expected_2 = Expected_1x*(1+trend);
obs1 + 1;
output;
end;
Run;
Post your data as text, specifically what your input data will look like.
Exactly what you're starting with is helpful. Your solution appears overcomplicated at this point.
And will you have only two years or does this need to be generalized across multiple records.
Hi Reeza,
Attached is the text format dataset.
Yes, data will only have three years span. At least for remaining of this year.
The code that I provided above works great all the numbers coming as result are correct, the only issue is when the Prod_type changes from one to another, it breaks.
Basically, If I had only one Prod_type, the above code works.
Thanks.
Do you need just the final value or all the intermediary values as well?
Expected_1 and Expected_2 are my final values. So yes, I want these two.
Yes, indeed your program is more complicated than needed. In particular, you should take advantage of two features of the DATA step:
As a first effort, don't worry about carryover from prior product_types. Just see if you can use the LAG function to generate the 2 new variables EXP1 and EXP2. Once you've got that, then return to the task of setting EXP1 to missing for the first record of each type, and first 2 recs for EXP2.
Here's the starting data set, all sasified:
data have;
input PROD_TYPE $4.Calendar_year ADJUSTED_PMPM :dollar9.2 Trend percent3.;
datalines;
Advo 2015 $3,304.55 7%
Advo 2016 $3,145.41 7%
Advo 2017 $3,229.17 7%
GRP 2015 $2,956.19 1%
GRP 2016 $3,238.21 1%
GRP 2017 $4,184.82 1%
IFP 2015 $4,706.53 9%
IFP 2016 $4,634.87 9%
IFP 2017 $5,308.22 9%
run;
Wow!! This was great. Never used the lag function before, so didn't know.
Here is the code that I tried.
Data test_2;
set Non_engaged;
Format EXP1 EXP2 dollar10.2;
by prod_type;
EXP1=lag(adjusted_pmpm)*(1+trend);
EXP2=lag(exp1)*(1+trend);
if first.prod_type then do;
EXP1 = 0;
EXP2 = 0;
end;
Run;
Setting EXP1 to missing for the first record for each product type is easy using First.Product_Type. But how do you set first 2 recs for EXP2 to missing??
YOu can use lag function on the automatic dummies:
if lag(first.product)=1 then exp2=0;
But instead of setting to zero, why not set values to missing?
if first.product then call missing(exp1,exp2);
if lag(first.product)=1 then call missing(exp2);
The advantage of the call missing is that it can (1) accept a list of variable, and (2) that list can be a mixture of character and numeric variables.
Then you can also use the NMISS function on the 3 variables to determine when to calculate the average (by using the MEAN function).
Ohh I see. This is how you set the variable to missing.
I didn't know how to set the variable to missing that's why was setting them to 0.
Thanks a ton. I really appreciate all your time.
Before call missing was added to SAS, you would use an explicit assignment statement:
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.