Is it possible to use a table in an array statement?

Question

I'm trying to reference a table in an array to eventually use in process in a data step. Here is the code:

proc sql;
create table claims.iterations 
    as select Allowed_Claims, Px1 
    from simulation 
    order by Allowed_Claims, Px1;
quit;
data simulation_iterations;
    array Members {24} _temporary_ (5 6 8 10 12 15 20 25 30 40 50 60 70 80 
                                    90 100 125 150 175 200 250 300 400 500); 
    array ****** ;
    do i = 1 to 24;
    do j = 1 to Members{i}-1;
        Px2 = rand('Uniform',0,1);
        if Px2 > 0.5 then Px2=Px2;          
        output;
        end;
    output;
    end;
run;

is it possible to do so? Although it isn't shown, I want to use the table as in an attempt to linearly interpolate for the values in the "Members" array.

Edit:

I want to interpolate for Allowed_Claims based on a randomly generated value (from 0 to 1). The condensed table that I'm basing my linearly interpolation off of is listed below.

Allowed_Claims  Px1
25.224545313    0.0008129708
34.767797696    0.0014747668
35.367278291    0.0015210493
42.616660238    0.0021191153
52.495185148    0.0030343735
52.652599162    0.0030497846
53.26904904     0.0031103676
57.183442582    0.0035034118

For example, let's call this Process A, if Px2 = rand('Uniform',0,1) yields .0010, I want it to interpolate between the Allowed_Claims values where Px1 = 0.0008129708 and Px1 = 0.0014747668.

The role of the array is that it dictates how many iterations of Process A I want. The array is

Members {24} _temporary_ (5 6 8 10 12 15 20 25 30 40 50 60 70 80 
                                    90 100 125 150 175 200 250 300 400 500);

So when the loop starts, it will perform 5 iterations of Process A, thereby producing 5 interpolated "allowed_claims" values. I want the average of these five claims.

Then, the loop will continue and perform 6 iterations of Process A and produce 6 interpolated "allowed_claims" values. Again, I want the average of these 6 claims.

I want the output table to look like:

`

Member[i]    Average_Expected_Claims
   5              (average of 5 interpolated claims) 
   6              (average of 6 interpolated claims) 
   8              (average of 8 interpolated claims)

Joe · Accepted Answer

Here's the example of how to do this with a hash table. It's very similar to an array in this usage - since there's no "lookup key", we just load it up and create an iterator, then loop over the iterator, just like an array (except you can't grab the exact position). Sadly there's no VLOOKUP-type functionality that will give you the closest match - you have to iterate until you find your correct one.

This isn't the absolute most efficient process, in particular I make exactly one more iterator call than I need to (as I could store the last seen allowed claims in a variable). I don't bother because frankly it's not worth it, just calling the iterator one more time doesn't make much difference, but I suppose for efficiency you might want to do it differently if this takes a long time on your machine. I doubt it will.

*Just setting up the example iterations dataset;
data iterations;
  call streaminit(7);
  retain allowed_claims 20;
  do px1 = 0.05 to 1 by 0.05;
    allowed_claims + rand('Uniform')*5;
    output;
  end;
run;


data simulated_iterations2;
  call streaminit(8);      *init the random generator;
  array Members [5] _temporary_ (5 6 8 10 12);  *any number of members here is fine;
  if _n_ eq 1  then do;            *initialize the hash tables;
    if 0 then set iterations;      *defines the variables used;
    declare hash _iter(dataset:'iterations', ordered:'a');  *ordered = ascending - do not need a sort first;
    _iter.defineKey('px1');                    * key is artificial, but has to exist;
    _iter.defineData('px1','allowed_Claims');  * data variables to retrieve;
    _iter.defineDone();
    declare hiter hi_iter('_iter');            * the iterator object;

  end;

  do _i_member = 1 to dim(members);            * iterate over members array;
    call missing(claims_simulated);
    do _i_simul = 1 to members[_i_member];     * iterate for the member value;

      rand_value = rand('Uniform');            * the number to interpolate;
      do rc= hi_iter.first() by 0 until (hi_iter.next() ne 0 or px1 gt rand_value );
         *do nothing;                          * here we are doing the work in the DO statement, looping until we find the value greater than the rand value;
      end;
      ac_max = allowed_claims;                 * your 'max' interpolation value;
      rc = hi_iter.prev();                     * back up one;
      ac_min = allowed_claims;                 * your 'min' interpolation value;
      claims_simulated + (mean(ac_min,ac_max))/members[_i_member];  *add the mean of the two interpolation values divided by the total number we are going to do to the total - avoids us having to do another array to store these and get mean of;
      put rand_value= claims_simulated=;       *just for logging;
    end;
    putlog;
    output;
  end;

  stop;

run;

Is it possible to use a table in an array statement?

Answers (2)

Related Questions