Identifying spikes in Interest Rates

[SASd15]

Hi,

I am trying to identify the dates interest rates increased/decreased for a customer in a period of time.

INPUT

Date              CustomerAccount  InterestRate

1/1/2016        1                                 2

2/1/2016        1                                 2

3/1/2016        1                                 3

4/1/2016        1                                 3

1/1/2016        2                                 8

2/1/2016        2                                 8

3/1/2016        2                                 9

4/1/2016        2                                 9

OUTPUT

Date            CustomerAccount  PreviousRate   New Rate

3/1/2016       1                               2                         3

3/1/2016       2                               8                         9

I appreciate your help.

[ChrisBrooks]

If you put the sort and second data step inside a code node in EG this should work

data have;
	infile datalines dlm=",";
	input date mmddyy8. CustomerAccount InterestRate;
	format date ddmmyy10.;
	datalines;
1/1/2016,1,2
2/1/2016,1,2
3/1/2016,1,3
4/1/2016,1,3
1/1/2016,2,8
2/1/2016,2,8
3/1/2016,2,9
4/1/2016,2,9
;
run;

proc sort data=have out=sorted;
	by CustomerAccount date;
run;

data want(keep= date CustomerAccount PreviousRate NewRate);
	set sorted;
	by CustomerAccount;
	PreviousRate=lag(InterestRate);
	if not first.CustomerAccount then do;
		if PreviousRate ne InterestRate then do;
			NewRate=InterestRate;
			output;
		end;
	end;
run;

[ballardw]

Here's one way:

data want;
   set have;
   by CustomerAccount;
   PreviousRate=Lag(InterestRate);
   if not(first.customeraccount) and InterestRate ne PreviousRate;
run;

though I didn't rename interestrate.

Lag is a function that allows examinging values from previous records but be careful using it directly in conditional statements as the procedure maintains a queue of values that likely do not match your expectations.

[kiranv_]

proc sort data=have;
by customeraccount date;
run;

data want;
set have;
by CustomerAccount;
if InterestRate -lag(interestrate) ne 0;
run;

proc sql;
select max(date) as date format= ddmmyy10., customeraccount,
max(previousrate) as previousrate, max(newrate) as newrate from
(select max(date) as date format =ddmmyy10., customeraccount,
case when date =min(date) then interestrate end as previousrate,
case when date =max(date) then interestrate end as newrate
from want
group by customeraccount)
group by customeraccount;

something like this

[Ksharp]

data have;
	infile datalines dlm=",";
	input date mmddyy8. CustomerAccount InterestRate;
	format date ddmmyy10.;
	datalines;
1/1/2016,1,2
2/1/2016,1,2
3/1/2016,1,3
4/1/2016,1,3
1/1/2016,2,8
2/1/2016,2,8
3/1/2016,2,9
4/1/2016,2,9
;
run;

data want;
 set have;
 by CustomerAccount;
 pre_rate=lag(InterestRate);
 flag=sign(dif(InterestRate));
 if first.CustomerAccount then flag=0;
 if flag=1;
run;