topic Re: Errors in Key-Indexing Subset Using Array in SAS Programming

Errors in Key-Indexing Subset Using Array

PegaZeus — Thu, 02 Apr 2020 19:06:18 GMT

Greetings,

So I have two tables, one of them with around 100,000,000 healthcare claims (claims), and one with a list of NDCs (ndc_table). I'm trying to use a key-indexing approach to subset to include only those claims associated with NDCs in the NDC table.

data final; 
  array ndc_list (*) _temporary_; 
  do until (eof1); 
    set ndc_table end=eof1; 
        ndc_list(ndc) = ndc; 
end;
do until (eof2); 
  set claims end=eof2; 
    array_search = ndc_list(ndc); 
    if array_search > . then output; 
end;
run;

I'm getting 3 errors that I can't seem to figure out:

ERROR: The non-variable based array ndc_list has been defined with zero elements.

ERROR: Too many array subscripts specified for array ndc_list.

I used (*) because the number of NDCs in the ndc_table is going to change over time. Right now there are around 3500 values.

Re: Errors in Key-Indexing Subset Using Array

FreelanceReinh — Thu, 02 Apr 2020 19:41:01 GMT

Hello @PegaZeus,

Arrays in SAS need to be defined with a dimension. In your case you may want to choose a "large enough" value, e.g., 999999 if this is larger than the maximum NDC value you would expect. Or determine the maximum in a preliminary step and use it in the array definition. SAS hash objects do not have this requirement. They grow as needed while they are loaded with data. So this would be an alternative approach.

Re: Errors in Key-Indexing Subset Using Array

PeterClemmensen — Thu, 02 Apr 2020 19:57:29 GMT

@PegaZeus what is the maximum value of ndc?

Re: Errors in Key-Indexing Subset Using Array

PegaZeus — Thu, 02 Apr 2020 20:13:44 GMT

The max value of NDC would be 99999999999. This is not the number of ndcs in the ndc table though. In the ndc table there are about 3500 NDCs.

Re: Errors in Key-Indexing Subset Using Array

PeterClemmensen — Thu, 02 Apr 2020 20:26:46 GMT

So NDC can range from 1 to 99999999999? Or does 99999999999 have some special meaning?

Re: Errors in Key-Indexing Subset Using Array

FreelanceReinh — Thu, 02 Apr 2020 20:39:26 GMT

@PegaZeus wrote:

The max value of NDC would be 99999999999. This is not the number of ndcs in the ndc table though. In the ndc table there are about 3500 NDCs.

This 11-digit number would likely be too large as an array dimension, so the key-indexing approach would need a modification or perhaps you just resort to the hash object approach shown below:

data final;
dcl hash h(dataset:'ndc_table');
h.definekey('ndc');
h.definedone();
do until(eof);
  set claims end=eof;
  if h.check()=0 then output;
end;
stop;
run;

Re: Errors in Key-Indexing Subset Using Array

PegaZeus — Thu, 02 Apr 2020 20:40:44 GMT

That's correct, except instead of just 1, it would be 00000000001. It has to have 11 digits.

Re: Errors in Key-Indexing Subset Using Array

Tom — Thu, 02 Apr 2020 20:41:01 GMT

The reason you are getting the error is because you haven't told SAS how many elements the array will have. Normally you don't have to tell SAS that because it can count the variables you listed. But since you are making a temporary array there aren't any variables to count.

Use a HASH object instead.

NDC codes are not a good domain to use as the index into an array. Especially since they are best represented as TEXT variables so that you an put the hyphens in the right places.

Re: Errors in Key-Indexing Subset Using Array

PeterClemmensen — Thu, 02 Apr 2020 20:44:34 GMT

Ok. Definitely go with the Hash approach suggested by @FreelanceReinh then 🙂

Re: Errors in Key-Indexing Subset Using Array

PeterClemmensen — Thu, 02 Apr 2020 20:47:58 GMT

@FreelanceReinh out of interest, what modification do you have in mind to approach this problem with key-indexing? Bitmapping?

Re: Errors in Key-Indexing Subset Using Array

PegaZeus — Thu, 02 Apr 2020 20:54:50 GMT

Okay I found out that what we actually want is not to subset the claims but simply to left join on the ndc key and bring in the other fields from the ndc table.

So I wrote a hash left lookup like this, however my output gives me blank values in the entire dataset. Nothing is populated.

data TEST(drop=rc:);
  if 0 then set ndc_table;
  if _N_ = 1 then do;
      declare hash h1(dataset:"ndc_table");
      h1.defineKey('NDC');
      h1.defineData(all:'Y');
      h1.defineDone();
  end;
  set claims;
  rc1=h1.find();
  if rc1 ne 0 then call missing(of _all_);
run;

Re: Errors in Key-Indexing Subset Using Array

FreelanceReinh — Thu, 02 Apr 2020 20:55:27 GMT

Oh, nothing concrete yet. I only remembered those Dorfman papers about key-indexing and was thinking that the NDC numbers perhaps could be mapped to a much smaller range of integers (without collisions). But this would, of course, require deeper knowledge about the structure of those NDC numbers.

Re: Errors in Key-Indexing Subset Using Array

Tom — Thu, 02 Apr 2020 21:04:08 GMT

When FIND() fails you a setting ALL of the variables to missing, even those the came from the CLAIMS dataset.
If everything ends up missing then you got no hits. Are you sure NDC variable is in both datasets? Is the content in the same format? Perhaps one is using NDC-10 and the other NDC-11.

Re: Errors in Key-Indexing Subset Using Array

PeterClemmensen — Thu, 02 Apr 2020 21:04:09 GMT

Ok. Thank you. Triggered my curiosity 🙂 The Dorfman papers entered my mind as well.

Re: Errors in Key-Indexing Subset Using Array

PegaZeus — Thu, 02 Apr 2020 21:32:37 GMT

Interesting, you're right. What actually happened was I was using too small of a claims sample for testing, which didn't include any of the NDCs in the ndc_table. I updated the call missing to only the fields in that table now.

Re: Errors in Key-Indexing Subset Using Array

mkeintz — Fri, 03 Apr 2020 00:03:25 GMT

@PegaZeus wrote:

Okay I found out that what we actually want is not to subset the claims but simply to left join on the ndc key and bring in the other fields from the ndc table.

So I wrote a hash left lookup like this, however my output gives me blank values in the entire dataset. Nothing is populated.
data TEST(drop=rc:);
  if 0 then set ndc_table;
  if _N_ = 1 then do;
      declare hash h1(dataset:"ndc_table");
      h1.defineKey('NDC');
      h1.defineData(all:'Y');
      h1.defineDone();
  end;
  set claims;
  rc1=h1.find();
  if rc1 ne 0 then call missing(of _all_);
run;

If it's a "left join on the ndc key", then you need an "if test" to avoid outputing claims that don't match the ndc table. So instead of

rc1=h1.find();
if rc1 ne 0 then call missing(of _all_);

you're probably better off with

if h1.find()=0  then output;

If the NDC table is a miniscule fraction of the claims dataset, may be there are a few cases that aren't all blank … but since your program produces a dataset with the same number of records as claims, maybe you didn't see them.

And of course you can eliminate the "drop=rc1" option in the data statement.