How to check Decline trend and Upward trend Deviation?

[Nav_Sweeney]

Dear All,

I have a dataset with daily balances of clients. I want to identify,check and flag out those clients who exhibit a "continuous" decline or growth trend in balances in quarters and half a year. In other words, i wanna identify and flag out those clients separately who exhibit a "1. continous decline",  "2. continuous growth", "3. Mixed trend- Both growth and decline" in four different quarters in a year and two half year(6 months period).

clientid           Date                     Balances

1100020       07/02/2011            37897

1100020       07/03/2011             89789

1100345        07/02/2011            78989

1100768        08/04/2011            768687

The dataset has 30 million records with information of daily balances of many clients.I do not have SAS ETS, so a datastep/Proc sql logic alone would be most appreciated. Nevertheless I would be excited to see how easily it can be done using SAS ETS. Thanks.

Nav

[PGStats]

Here is a way to get the proportion of increases and decreases for each client and date period :

/* Test data including increases, decreases and mixed cases */

data test;
input clientId dateBal :mmddyy10. Balance;
datalines;
1100020       07/02/2011            37897
1100020       07/03/2011             89789
1100345        07/02/2011            78989
1100345        07/03/2011            78988
1100345        07/04/2011            78987
1100345        07/05/2011            78986
1100768        08/04/2011            768687
1100768        08/05/2011            768688
1100768        08/06/2011            768687
;

/* Assuming the data is already sorted by clientId and date*/

data diffs;
do until(last.clientId);
     set test; by clientId;
     diff = balance - lastBalance;
     output;
     lastBalance = balance;
     end;
drop balance lastBalance;
run;

/* List the date groupings that you want */
data periods;
input periodType $;
datalines;
QTR
SEMIYEAR
;

/* Get the proportion of inceases and decreases for each client and period */
proc sql;
create table counts as
select clientId,
    periodType,
    intnx(periodType, dateBal, 0) as period format=mmddyy10.,
    sum(diff>=0)/count(diff) as propIncr format=percentn.,
    sum(-diff>=0)/count(diff) as propDecr format=percentn.
from diffs cross join periods
group by clientId, periodType, period;
quit;

PG

[udo_sas]

Hello -

This example illustrates using the TIMESERIES procedure of SAS/ETS for trend and seasonal analysis of time-stamped transactional data:

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_timeseries_sec...

Thanks,

Udo

[Karteek]

HI, I have a similar dataset where I need to build predictive model for Balance as function of Time and independent predictors (like FICO which is not time varying)  for a transactional data. Could anyone share any experiences and thoughts. Can we model this using proc ucm?

Thanks

[udo_sas · Accepted solution]

Hello -

Yes, the MODEL statement of PROC UCM (SAS/ETS) allows you to specify a response variable and, optionally, the predictor or regressor variables for the UCM model. The predictors specified in the MODEL statement are assumed to have a linear and time-invariant relationship with the response. If your predictors have time-varying regression coefficients you can specify them separately in the RANDOMREG statement. Similarly, if the predictors have a nonlinear effect on the response variable you can specify them separately in the SPLINEREG statement.

For more details on the UCM procedure you may want to check out: http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_ucm_sect001.ht...

Thanks!

Udo