MAX and SUM on different columns

[udupa_13]

I am trying to group by USERID to know MAX of TRANSACTION_DATE and on top of that want to sum AMOUNT only for those MAX of TRANSACTION_DATE. Any optimal solution?

[Kurt_Bremser · Accepted solution]

Try

proc sql;
create table want as
  select
    userid,
    transaaction_date,
    amount_sum
  from (
    select
      userid,
      transaction_date,
      sum(amount) as amount_sum
    from have
    group by userid, transaction_date
  )
  group by userid
  having transaction_date = max(transaction_date)
;
quit;

Untested; for tested code, please supply source data in a data step with datalines.

[Reeza]

So SUM of a variable at the latest date?

[udupa_13]

Yes

[Reeza]

proc sql;
create table want as
select
  userid,
 transaaction_date,
 sum(amount) as amount_sum
  from have
  group by userid
  having transaction_date = max(transaction_date)
;
quit;

You can simplify @Kurt_Bremser query to the above in SAS at least.

[andreas_lds]

---

@udupa_13 wrote:

I am trying to group by USERID to know MAX of TRANSACTION_DATE and on top of that want to sum AMOUNT only for those MAX of TRANSACTION_DATE. Any optimal solution?

---

Optimal depends on the perspective: an administrator will consider a solution optimal if it does not use to much of the precious resources a server has, someone else will want as few lines of code as possible and another one does not care about the number of lines, but wants code that can be read easily.