Where, Having and OR clauses

Question

I am hoping for your help! I have a set with approved, decline, incomplete, etc transactions. I need to get the latest transaction (due to duplication of transactions; they only differ on the status Approve/decline/etc), but if we need to keep all approved (one transaction can be approved twice, we need to see it). Task: I need to keep all of the "Approved" transactions, and then just the latest (from the rest of the results). Not all of the transactions have Approved status. One transaction can have 2 Approvals, while other transaction can have Decline and Decline (in such case I need the latest decline).

This is not working:

proc sql;
    create table XYZ as 
    select * 
    from MKZ
    where approve_decline='Approve'
    group by Trans_id_proxy 
    or (having datetimevar=max(datetimevar)) 
    order by account_number
;
quit;

Thank you!

score 0 · Answer 1 · answered Mar 04 '21 at 01:28

If you're in SAS, then this is not a task for proc sql but for the data step. PROC SQL isn't very good at this kind of row-by-row consideration, particularly with order - it's possible to do it in SQL, but much more complicated.

In terms of what you're doing wrong - first, you don't actually want to group by, that collapses rows; you want to select individual rows. Having also is not something you can embed inside something else - where and having are separate, where is before the group by (so, filters the input records) and having is after the group by (filters the output records).

In the data step, this is pretty quick.

proc sort data=mkz out=mkz_sort;
  by account_number trans_id_proxy datetimevar;
run;

data xyz;
  set mkz_sort;
  by account_number trans_id_proxy;
  if (last.trans_id_proxy) or (approve_decline='Approve');
run;

I'm not sure I have the by correct, as it's not clear how you intend to group these, but I'm assuming account_number is the main thing to group by and trans_id_proxy is the secondary - you don't do it quite this way in SQL so maybe you mean something else. Just have the by have datetimevar at the end, and before it whatever variable(s) you want to operate on as grouping.

Dear, Joe! Thank you for the code. It works well for "approved" but if a customer has a transaction and it declines (without approving), the code does not pick it up. Here is an example: I want to purchase an airline ticket at 14:13:23.789 and my card declines. The code does not pick it up. Or I do the same transaction but it tired twice (at the same exact time), the code should pick up the latest transaction (datetimevar). The datetimevar always has a "lag" in time from the original transaction. So no issues here. However, the code does not pick up any of the "declines". Can you, please, help — Yuliya Love K Lynch, Mar 09 '21 at 22:06

Where, Having and OR clauses

1 Answers1