Phasing across Variables Issue

[t_kingsbury]

Hi

I have a issue that I am currently trying to solve, however I have not been having much luck on google or forum searches

I have a situation where I need to calculate the worst status a customer has filed in 12 months - I have 96 months of data - 1 month = 1 variable so I have 96 variables

My issue arrises where customers can begin and end at different points - I need to find a function or macro where I can scan across the variables to find the most recent month a customer filed a status, and then do a IF MAX calculation from that point

Because customers begin and end at different points I am finding this quite difficult to master and was wondering if anyone had any previous experience or insight?

I have included a simplified example below where I have been testing based on worst status in last 3 months

If anyone has any advice I would really appreciate it

Tom

DATA example;

input cust_no mon1 mon2 mon3 mon4 mon5 mon6;

DATALINES;

1001 0 0 1 1 . .

1002 . . 0 0 4 4

1003 0 0 0 0 0 1

1004 . 1 2 3 4 .

;

[Kurt_Bremser]

Set up an array for the months:

array months {} mon1-mon96;

now you can iterate through that with months{x}

cust_start = 0;

cust_end = 0;

cust_max = 0;

do i = 1 to 96;

  if months{i} ne .

  then do;

    if cust_start = 0 then cust_start = i;

    cust_max = max(cust_max,months{i});

  end;

  else if cust_start ne 0 then cust_end = i - 1;

end;

should give you starting and ending month for a customer and the maximum value encountered

[Haikuo]

I am kinda slow on following your description. What will be the most recent month, the month1 or month96? What do mean by file a status, anything greater than 0? What's your definition by bad/worse/worst, is it higher the number, the worse the case? And What the outcome you are expecting given your presented data?

[t_kingsbury]

Hai,

I appreciate that my explanation could have been clearer, my apologies

The most recent month would be month96 - the window is a 96 month period (last 8 years)

As for the status, the status is defined anything not missing, in the case of my example 0,1,2,3 and 4 are statuses so 4 is the worst

The outcome I am looking for is to be able to calculate the max value, a defined number of periods back based on the last status filed - so in my example if you take customer 1002 - that customer is present in four periods, I would like to be able to find the last period that customer filed a status in, and then find the max value in the last 3 periods (the answer would be 4)

I am using shortened numbers in my example data to simplify the scenario

Kind Regards

Tom

[Kurt_Bremser]

In that case, you'd be better off in going through the array backwards from the end, register when the first non-missing column shows up, and then take the max for the next 4(3) iterations.