topic Re: MAX and SUM on different columns in SAS Programming

MAX and SUM on different columns

udupa_13 — Thu, 17 Dec 2020 12:57:33 GMT

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?

Re: MAX and SUM on different columns

Kurt_Bremser — Thu, 17 Dec 2020 13:16:10 GMT

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.

Re: MAX and SUM on different columns

Reeza — Thu, 17 Dec 2020 15:55:49 GMT

So SUM of a variable at the latest date?

Re: MAX and SUM on different columns

udupa_13 — Thu, 17 Dec 2020 16:17:59 GMT

Yes

Re: MAX and SUM on different columns

Reeza — Thu, 17 Dec 2020 16:59:05 GMT

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.

Re: MAX and SUM on different columns

andreas_lds — Fri, 18 Dec 2020 05:41:43 GMT

@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.