CODEWITHSUNDEEP
sas
Nika M
Nika M

Reputation: 1

How to use MS SQL window function in SAS proc SQL

Hi I am trying to calculate how much the customer paid on the month by subtracting their balance from the next month. Data looks like this: I want to calculate PaidAmount for A111 in Jun-20 by Balance in Jul-20 - Balance in June-20. Can anyone help, please? Thank you

enter image description here

Upvotes: 0

Views: 697

Answers (2)

Tom
Tom

Reputation: 51611

For this situation there is no need to look ahead as you can create the output you want just by looking back.

data have;
  input id date balance ;
  informat date yymmdd10.;
  format date yymmdd10.;
cards;
1 2020-06-01 10000
1 2020-07-01 8000
1 2020-08-01 5000
2 2020-06-01 10000
2 2020-07-01 8000
3 2020-08-01 5000
;

data want;
  set have ;
  by id date;
  lag_date=lag(date);
  format lag_date yymmdd10.;
  lag_balance=lag(balance);
  payment = lag_balance - balance ;
  if not first.id then output;
  if last.id then do;
    payment=.;
    lag_balance=balance;
    lag_date=date;
    output;
  end;
  drop date balance;
  rename lag_date = date lag_balance=balance;
run;

proc print;
run;

Result:

Obs    id          date    balance    payment

 1      1    2020-06-01     10000       2000
 2      1    2020-07-01      8000       3000
 3      1    2020-08-01      5000          .
 4      2    2020-06-01     10000       2000
 5      2    2020-07-01      8000          .
 6      3    2020-08-01      5000          .

Upvotes: 1

Reeza
Reeza

Reputation: 21274

This is looking for a LEAD calculation which is typically done via PROC EXPAND but that's under the SAS/ETS license which not many users have. Another option is to merge the data with itself, offsetting the records by one so that the next months record is on the same line.

data want;
merge have have(firstobs=2 rename=balance = next_balance);
by clientID;
PaidAmount = Balance - next_balance;
run;

If you can be missing months in your series this is not a good approach. If that is possible you want to do an explicit merge using SQL instead. This assumes you have month as a SAS date as well.

proc sql;
create table want as
select t1.*, t1.balance - t2.balance as paidAmount
from have as t1
left join have as t2
on t1.clientID = t2.ClientID
/*joins current month with next month*/
and intnx('month', t1.month, 0, 'b') = intnx('month', t2.month, 1, 'b');
quit;

Code is untested as no test data was provided (I won't type out your data to test code).

Upvotes: 0

Related Questions