BookmarkSubscribeRSS Feed
t_kingsbury
Calcite | Level 5

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 .

;

4 REPLIES 4
Kurt_Bremser
Super User

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
Onyx | Level 15

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
Calcite | Level 5

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 802 views
  • 4 likes
  • 3 in conversation