BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nav_Sweeney
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
udo_sas
SAS Employee

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

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

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

PG
udo_sas
SAS Employee

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
Calcite | Level 5

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
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

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.

Discussion stats
  • 4 replies
  • 2337 views
  • 1 like
  • 4 in conversation