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
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
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
Hello -
This example illustrates using the TIMESERIES procedure of SAS/ETS for trend and seasonal analysis of time-stamped transactional data:
Thanks,
Udo
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.