Case Statement and Aggregation within a Group By classe in proc sql

I am having some trouble aggregating and using case within a group.

The objective is to check Indicator for each transaction key. If ‘1’ indicator exists then we have to select the max(Change_Date). If all zeros then min(Change_Date). Along with that the Initial_key associated with that Change_date has to be populated as a Final_key.

The output looks like this

Answer

You can get the last two columns using aggregation. If I understand correctly:

select trxn_key,
       coalesce(max(case when indicator = 1 then change_date end),
                min(change_date)
               ) as final_date,
       coalesce(max(case when indicator = 1 then initial_key end),
                min(initial_key)
               ) as final_key
from t
group by trxn_key;

Then join this in:

proc sql;
    select t.*, tt.final_date, tt.final_key
    from t join
         (select trxn_key,
                 coalesce(max(case when indicator = 1 then change_date end),
                          min(change_date)
                         ) as final_date,
                 coalesce(max(case when indicator = 1 then initial_key end),
                          min(initial_key)
                         ) as final_key
          from t
          group by trxn_key
         ) tt
         on tt.trxn_key = t.trxn_key;