How to create rolling arrays in SAS

[cmahrb2]

I am trying to calculate the dosage of a particular drug for a population to see if any member in this population is over a certain threshold for any 90 consecutive days. So to do this I am thinking that I am going to need to make a arraty that looks at the strengh of this drug over 90 days from an index and if they are all '1' then they get a 'pass', and somehow put this into a do loop to look at all of the potential 90 day windows for a year i=1...i=275 to see if a member at any point during the year has met the criteria. Thoughts?

[Reeza]

Depends on your data structure. What does your data currently look like?

[cmahrb2]

What would you like to know ?

[Reeza]

What your data currently looks like? Is it formatted in a long or wide format? Do you have a record for every day or are you missing days? If wide, do you have a naming convention? It's much easier if you post sample data and sample output 🙂

[Jim_G]

Organize your data to have a threshold for each date for each patient id.

Sort by id date

read by id date

on first.id  store save_date, clear flag

check the threshold on each record

  if thrshold gt target

       subtract date-save_date

        if difference gt 90 set flag

       store save_date

on last.id check the flag

I hope this helps     Jim

[hbi]

Hi there,

Give this a try. I created some test data for you. I came up with two possible solutions. Enjoy.

hbi

/* not sure if your data actually looks like this; hopefully it's good enough to show these techniques */
/* create 1000 observations with 275 boolean variables */
DATA have;
  LENGTH ID 8 today_minus1-today_minus275 3;
  ARRAY num_array today_minus1-today_minus275;
  DO ID=1 TO 1000;
    DO OVER num_array;
      num_array = floor(ranuni(1234)+0.94);
    END;
    output;
  END;
RUN;


/* method 1: this is the easy way ... */
DATA want1(drop=today_minus1-today_minus275);
  SET have;

  LENGTH cat_275 $300 success_label $100
         find_90_days end_90_days 8;

  cat_275 = CATS(OF today_minus:);
  find_90_days = INDEX(cat_275, REPEAT('1', 90));
  IF find_90_days > 0 THEN DO;
    end_90_days = 275 - INDEX(COMPRESS(REVERSE(cat_275)), REPEAT('1', 90)) + 1;
    success_label = CAT("First occurrence of 90 consecutive days begins at position ", find_90_days, 
                        " and ends at position ", end_90_days);
  END;
RUN;


/* method 2: use arrays ... */
DATA want2(drop=array_loop consecutive_count);
  SET have;

  ARRAY num_array today_minus1-today_minus275;
  LENGTH array_loop consecutive_count best_count 8 success_label $100;

  array_loop        = 0;
  consecutive_count = 0;
  best_count        = 0;

  DO OVER num_array;
    array_loop + 1;
    IF num_array = 1 THEN DO;
      consecutive_count + 1;
      IF consecutive_count >= best_count THEN best_count = consecutive_count;
      IF consecutive_count >= 90 THEN success_label = CAT('First occurrence of 90 consecutive days detected at position: ', array_loop);
    END;
    ELSE consecutive_count = 0;
  END;

[hbi]

Attaching a screenshot of the output window (method 1):