topic Create rate based on previous date; in SAS Programming

Create rate based on previous date;

devnand — Tue, 18 Oct 2016 21:39:44 GMT

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.

Re: Create rate based on previous date;

Reeza — Tue, 18 Oct 2016 23:03:29 GMT

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?

Re: Create rate based on previous date;

devnand — Tue, 18 Oct 2016 23:07:38 GMT

Yes, it is from scratch.

Re: Create rate based on previous date;

Reeza — Wed, 19 Oct 2016 03:30:38 GMT

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.

Re: Create rate based on previous date;

PGStats — Wed, 19 Oct 2016 03:43:12 GMT

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;