topic Re: Identifying spikes in Interest Rates in SAS Enterprise Guide

Identifying spikes in Interest Rates

SASd15 — Wed, 19 Jul 2017 14:42:12 GMT

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.

Re: Identifying spikes in Interest Rates

ChrisBrooks — Wed, 19 Jul 2017 14:59:55 GMT

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;

Re: Identifying spikes in Interest Rates

ballardw — Wed, 19 Jul 2017 15:04:20 GMT

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.

Re: Identifying spikes in Interest Rates

kiranv_ — Wed, 19 Jul 2017 15:08:50 GMT

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

Re: Identifying spikes in Interest Rates

Ksharp — Thu, 20 Jul 2017 14:01:44 GMT

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;