topic Re: Calculating Arrears in SAS Programming

Calculating Arrears

Adnan2 — Mon, 02 Sep 2024 11:59:25 GMT

Hi All,

I have the following data

accountid	Month	Credit	Debit	Month_End_Balance	Min_Pay
123	202310	.	565.54	565.54	12.56
123	202310	565.54	.	565.54	12.56
123	202311	.	317.63	883.17	18.98
123	202311	317.63	.	883.17	18.98
123	202312	.	1184.24	2067.41	45.98
123	202312	1184.24	.	2067.41	45.98
123	202401	.	208.1	2275.51	47.32
123	202401	208.1	.	2275.51	47.32
123	202402	.	-1311.05	964.46	19.87
123	202402	-1311.05	.	964.46	19.87
456	202310	.	31.86	31.86	3.09
456	202311	.	845.94	877.8	18.76
456	202311	845.94	.	877.8	18.76
456	202312	.	-415.08	462.72	9.34
456	202312	-415.08	.	462.72	9.34
456	202401	.	-71.94	390.78	8.76
456	202401	-71.94	.	390.78	8.76
456	202402	.	657.29	1048.07	20.08
456	202402	657.29	.	1048.07	20.08

1.) how can I have a single record per month per account. At the moment the credits and debits are being recorded on separate records

2.) from the data above how can I calculate the arrears by month?

Thanks in advance

Re: Calculating Arrears

Astounding — Mon, 02 Sep 2024 12:16:16 GMT

Given that your data set is sorted this would be one way:

proc summary data=have;
  var credit debit min_pay;
  by accountid month;
  out=want (drop=_: ) max=;
run;

Re: Calculating Arrears

mkeintz — Mon, 02 Sep 2024 22:17:28 GMT

Assuming data are sorted by accountid/month and each id/month has either a qualifying credit obs or qualifying debit obs (or both) you can:

data want;
  merge have (where=(credit^=.))  have (where=(debit^=.));
  by accountid month;
run;

And what is your project's operational definition of arrears?

Edited note: Go see @Astounding's catch of my oversight. (Should have had ("drop = credit") as an additional data set name parameter in the 2nd argumnet of the MERGE statement.

Re: Calculating Arrears

Astounding — Mon, 02 Sep 2024 17:33:21 GMT

An interesting approach, which would work with a slight tweak:

data want; merge have (where=(credit^=.)) have (drop=credit where=(debit^=.)); by accountid month; run;

It also suggests another possibility:

data want; update have (where=(credit^=.)) have; by accountid month; run;

Re: Calculating Arrears

mkeintz — Mon, 02 Sep 2024 22:14:34 GMT

@Astounding wrote:

An interesting approach, which would work with a slight tweak:

data want; merge have (where=(credit^=.)) have (drop=credit where=(debit^=.)); by accountid month; run;

It also suggests another possibility:

data want; update have (where=(credit^=.)) have; by accountid month; run;

Oops. Thanks

Re: Calculating Arrears

SASKiwi — Mon, 02 Sep 2024 22:23:36 GMT

What is your definition of arrears? An actual example would be good. There are many different ways to calculate arrears so you need to tell us what your method is.

Re: Calculating Arrears

mkeintz — Mon, 02 Sep 2024 22:32:27 GMT

Or, as I now recall, a solution frequently posted by others:

data want;
  update have (obs=0) have;
  by accountid month;
run;