Calculating Arrears

[Adnan2]

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 

[Astounding]

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;

[mkeintz]

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.

[Astounding]

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;

[mkeintz]

---

@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

[mkeintz]

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

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

[SASKiwi]

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.