Create rate based on previous date;

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Create rate based on previous date;

I am trying to create a dataset with yearmonth and the decreasing rate.

 

I want to create a dataset with date variable and average rate decaying at a fix rate; After 50 months I want the value to be equal to the

to avg_date as after certain time peroid it will be zero and negative.

 

%let avg_dt=0.02345;
%let avg_date=0.01234;
%let projected_month=50;

 

This give of rate of decrease to be 0.00016

 

For first obs,  date=201708 and rate=0.02345;

for second obs, date=201708 and rate=(0.02345-00016)=0.023289

for third obs, date=201708 and rate=0.023289-00016=0.023128

so that the fifth obs rate=0.01234;

for the date value after that, i want to retain this value.

 

 

 

data dates;
      
         format date yymmn6.;
             date=201601;
         do while(date=202501);
             output;
             date=intnx('month', date, 1);
               rate=lag(&avg_dt)-00016;
          end;
         run;

 

Thank you in advance.


Accepted Solutions
Solution
‎10-19-2016 10:08 AM
Respected Advisor
Posts: 4,804

Re: Create rate based on previous date;

[ Edited ]

Have your pick between rate1 and rate2 :

 

%let avg_dt=0.02345;
%let avg_date=0.01234;
%let projected_month=50;
%let startMonth=01AUG2017;

data rates;
do i = 0 to &projected_month;
    month = intnx("MONTH", "&startMonth"d, i);
    rate1 = &avg_dt + (&avg_date-&avg_dt)*i/&projected_month;
    rate2 = &avg_dt * (&avg_date/&avg_dt)**(i/&projected_month);
    output;
    end;
format month yymmn6. rate1 rate2 8.5;
drop i;
run;

proc print; run;
PG

View solution in original post


All Replies
Super User
Posts: 19,039

Re: Create rate based on previous date;

The program here depends highly on what you start with.

What does your starting data set look like, or are you creating one from scratch?

Contributor
Posts: 39

Re: Create rate based on previous date;

Yes, it is from scratch.
Super User
Posts: 19,039

Re: Create rate based on previous date;

You've assumed month is holding its value from line to line. 

 

Treat the rate variable the same. 

 

Dont use lag function, replace it with Rate. This won't answer all your questions but should get you started in right direction. 

Solution
‎10-19-2016 10:08 AM
Respected Advisor
Posts: 4,804

Re: Create rate based on previous date;

[ Edited ]

Have your pick between rate1 and rate2 :

 

%let avg_dt=0.02345;
%let avg_date=0.01234;
%let projected_month=50;
%let startMonth=01AUG2017;

data rates;
do i = 0 to &projected_month;
    month = intnx("MONTH", "&startMonth"d, i);
    rate1 = &avg_dt + (&avg_date-&avg_dt)*i/&projected_month;
    rate2 = &avg_dt * (&avg_date/&avg_dt)**(i/&projected_month);
    output;
    end;
format month yymmn6. rate1 rate2 8.5;
drop i;
run;

proc print; run;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 323 views
  • 1 like
  • 3 in conversation