Sample DATA Step Scenarios

/*
* sort claims by group (loss cause) and reserve to compute rank
*/

data ranked_claims;
  set &input_dataset;
  by loss_cause descending reserve;
  retain;

  if first.loss_cause then do;
    rank = 0;
  end;

  rank = rank + 1;
  output;
run;

/*
* compute first_pass table with average reserve per loss cause
*/
data first_pass;
   set &input_dataset;
   by loss_cause;
   retain count sum mean;
   keep loss_cause count sum mean;

   if first.loss_cause then do;
      count = 0;
      mean = 0;
      sum = 0;
   end;

   count = count + 1;
   sum = sum + reserve;

   if last.loss_cause then do;
      mean = sum/count;
      output;
   end;
run;

/*
* compute stats table with mean, variance, std deviation per loss cause
*/
data stats;
  merge &input_dataset first_pass;
  by loss_cause;
  retain agg_deviation variance std_deviation;
  keep loss_cause count sum mean agg_deviation variance std_deviation;

  if first.loss_cause then do;
     agg_deviation = 0;
     variance = 0;
     std_deviation = 0;
  end;

  sq_distance = (reserve - mean) ** 2;
  agg_deviation = agg_deviation + sq_distance;

  if last.loss_cause then do;
    variance = agg_deviation / count;
    std_deviation = sqrt(variance);
    output;
  end;
run;

/*
* merge stats back to ranked_claims to have all info in one table
*/
data claims_with_stats;
    merge ranked_claims stats;
    by loss_cause;
run;

/*
* look for claims whose loss_cause is has a reserve more than the mean and a z-score greater than 1.5
*/
data &output_dataset;
  set claims_with_stats;

  z_score = (reserve - mean) / std_deviation;

  /* look for claim reserve outside 3 std deviations
   * (99.7% under normal distribution) for the group
   */

  if z_score > 1.5 and reserve > mean then do;
    &capture_num_value = reserve;
    &message = "Outlier claim reserve.";
    &message = &message || " μ = " || put(mean,10.2) || " σ = " ||
       put(std_deviation, 10.2) ||  " Rank = " || put(rank, 10.) ||
       " for group = " || loss_cause; &score = floor(5 * z_score);
     %event;
  end;
run;

data &OUTPUT_DATASET;
    retain p10005_sum &TRIG_TXNS;
    set &INPUT_DATASET;
    
    by party_number date_key;

    if (first.party_number or first.date_key) then do;
        p10005_sum = 0;
        &TRIG_TXNS = '';
    end;

    if (upcase(currency_based_account_ind) in ('N') and
        upcase(account_type_desc) in ('P') and
        upcase(primary_medium_desc) in (&p_PrimaryMedium) and
        upcase(transaction_cdi_code) in ('C') and
        upcase(status_desc) in ('SUCCESS') and
        upcase(currency_code) in ('USD')) then do;

        p10005_sum = sum(p10005_sum, currency_amount);
        if (&TRIG_TXNS = '') then do;
            &TRIG_TXNS = strip(transaction_key);
        end;
        else do;
            &TRIG_TXNS = catx(',', &TRIG_TXNS, strip(transaction_key));
        end;
    end;
    
    &START_DATE = '01AUG2007:00:00:00'dt;
    &END_DATE = '03AUG2007:23:59:59'dt;
    VSD_ORIG_DATEDT = dhms(date_key,23,59,59);

    if (last.party_number or last.date_key) then do;
        if (p10005_sum > &p_Threshold) then do;
            &MESSAGE = compbl('Daily cash deposit total '||currency_code||' 
                '||p10005_sum|| ' exceeds '||&p_Threshold||' threshold.');
            &SCORE = p10005_sum;

            if (p10005_sum <= &max_score) then do;
                &SCORE = p10005_sum/&max_score;
            end;
            else do;
                &SCORE = 1;
            end;

            &CAPTURE_NUM_VALUE = &score;
            &CAPTURE_CHAR_VALUE = primary_medium_desc;
            %EVENT;
        end;
    end;
run;

data foo;
  set &INPUT_DATASET;
  &START_DATE = <your code here>;
  &END_DATE = &runasofdate;
run;

data bar;
  retain <your variables here>;
  length &TRIG_TXNS varchar(*)
    VSD_EVENT_TRIGGER_TEXT varchar(1024)
    VSD_CAPTURE_CHAR_VALUE varchar(500);

  set foo;
  by <your variables here>;
    
  if first.<your variables here> then do;
    <your code here>
 end;

  <your code here>
    
  if last.<your variables here> then do;
    output;
  end;
run;
    
data &OUTPUT_DATASET;
  set bar;
  if <your code here> then do;
      %EVENT;
  end;
  else do;
      %NON_EVENT;
  end;
run;