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

Hi Folks,

 

I'm struggling to write a program for trend analysis, below is screenshot of what I am trying to accomplish

 

Excel.png

 

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

Excel2.png

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

Yes, indeed your program is more complicated than needed.  In particular, you should take advantage of two features of the DATA step:

  1. The LAG function, which allows you to access data from preceding records (actually preceding invocation of a lag queue - which in this case is the same thing.  For instance you can
        EXP1=lag(adjusted_pmpm)*(1+trend)
    and
        EXP2=lag(exp1)*(1+trend)
    You should probably nest those expressions in a ROUND function to get exact pennies.

  2. The BY statement (as in SET HAVE; BY PRODUCT_TYPE;) which allows you to determine whether the record-in-hand is the first record for a product_type (via the automatic variable FIRST.PRODUCT_TYPE).  You can use this information to eliminate the unwanted consequences of lagged values carrying over from one product_type to the next:

 

 

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

10 REPLIES 10
Abhi_Garg
Obsidian | Level 7

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;
Reeza
Super User

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.

Abhi_Garg
Obsidian | Level 7

Hi Reeza,

 

Attached is the text format dataset.

 

Yes, data will only have three years span. At least for remaining of this year. Smiley Tongue

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.

 

Reeza
Super User

Do you need just the final value or all the intermediary values as well?

Abhi_Garg
Obsidian | Level 7

Expected_1 and Expected_2 are my final values. So yes, I want these two.

mkeintz
PROC Star

Yes, indeed your program is more complicated than needed.  In particular, you should take advantage of two features of the DATA step:

  1. The LAG function, which allows you to access data from preceding records (actually preceding invocation of a lag queue - which in this case is the same thing.  For instance you can
        EXP1=lag(adjusted_pmpm)*(1+trend)
    and
        EXP2=lag(exp1)*(1+trend)
    You should probably nest those expressions in a ROUND function to get exact pennies.

  2. The BY statement (as in SET HAVE; BY PRODUCT_TYPE;) which allows you to determine whether the record-in-hand is the first record for a product_type (via the automatic variable FIRST.PRODUCT_TYPE).  You can use this information to eliminate the unwanted consequences of lagged values carrying over from one product_type to the next:

 

 

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Abhi_Garg
Obsidian | Level 7

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??

 

mkeintz
PROC Star

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).

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Abhi_Garg
Obsidian | Level 7

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.  

mkeintz
PROC Star

Before call missing was added to SAS, you would use an explicit assignment statement:

  1. To set a numeric variable to missing:    X=.;
  2. To set a character variable to missing    CVAR='';
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 10 replies
  • 1699 views
  • 5 likes
  • 3 in conversation