topic Re: How to use array to create a lookup table? in New SAS User

How to use array to create a lookup table?

learnin — Mon, 22 Apr 2019 09:05:24 GMT

The question is about calculating the minimum fee. I have a dataset called busfares, which contains different station code, the destation code, type of fares. So much thanks to all of you if you wish to spend some time on my question.

The dataset looks like this.

srt_na srt srt_id dest_na dest dest_id adult_pri student_pri child_pri eldery_pri
abcde  abc  1     abcde    abc   1        0       0           0         0
abcde  abc  1     bbcde    bbc   2        5       4           4         1
abcde  abc  1     cbcde    cbc   3        7       5           5         1
abcde  abc  1     dbcde    dbc   4        7       5           5         1
abcde  abc  1     ebcde    ebc   5        10      6           6         1
                                  .
                                  .
                                  .
abcde  abc  1     ooooo    ooo   91       30.5    20          20        1
bbcde  bbc  2     abcde    abc    1       5       4           4         1
bbcde  bbc  2     bbcde    bbc    2       0       0           0         0
                                  .
                                  .
                                  .
ooooo  ooo  91    abcde    abc    1       30.5    20          20        1
ooooo  ooo  91    bbcde    bbc    2       30.5    20          20        1
                                  .
                                  .
                                  .
ooooo  ooo  91    ooooo    ooo    91      0       0           0

The question is how can I use four array to bulid the look up tables for different type of fares? The table should be look like that. And the row and column are representing different station name like row1 is representing abc, column1 is also representing abc as well

adult   1   2   3   4   ...
1       0   5   7   7   ...
2       5   0   10  10  ...
3       7   10  0   5   ...
4       7   10  5   0   ...

Re: How to use array to create a lookup table?

Astounding — Mon, 22 Apr 2019 11:19:29 GMT

How can you compute the minimum fee, when your data contains no fee information?

Give an example of how to do that if you had no computer, working only with the printed data.

Re: How to use array to create a lookup table?

learnin — Mon, 22 Apr 2019 11:23:35 GMT

No, there is different fare in the dataset. For example, adult_pric is actually the price for an adult traveling from one station to another station. Like 5(adult_fare) means that person travel from station "abcde" to station "bbcde".

Re: How to use array to create a lookup table?

Astounding — Mon, 22 Apr 2019 11:36:31 GMT

OK. For that you can create a hash table directly from the original data. I leave the task to others with better hashing skills than mine

Re: How to use array to create a lookup table?

learnin — Mon, 22 Apr 2019 11:48:38 GMT

I am sorry, I have to use the array technique to finish this task. Since this is the requirement.

Re: How to use array to create a lookup table?

Astounding — Mon, 22 Apr 2019 12:40:10 GMT

Then you can choose between a three dimensional array, or 4 two dimensional arrays.

For a single array:

data want;

Array plist [4, 91, 91] ;
set have;
plist [1, srt_id, dest_id] = adult_pri;
plist [2, srt_id, dest_id] = student_pri;
etc.

Re: How to use array to create a lookup table?

Astounding — Mon, 22 Apr 2019 14:43:10 GMT

Now that I have my hands on a real keyboard instead of a handheld, here's a way to set up 4 two-dimensional arrays:

data want;
   if _n_=1 then do;
      array adult {91, 91} _temporary_;
      array student {91, 91} _temporary_;
      array child {91, 91} _temporary_;
      array elderly {91, 91} _temporary_;
      do until (done);
         set have end=done;
         adult{srt_id, dest_id} = min(adult_pri, adult{srt_id, dest_id} ) ;
         student{srt_id, dest_id} = min(student_pri, student{srt_id, dest_id} ) ;
         child{srt_id, dest_id} = min(child_pri, child{srt_id, dest_id} ) ;
         elderly{srt_id, dest_id} = min(elderly_pri, elderly{srt_id, dest_id} ) ;
      end;
   end;
 
   * more DATA step code here to utilize the arrays;

The original question mentions something about finding the minimum price, which is the reason for applying the MIN function. It's possible that I'm overcomplicating the problem in that regard.

Do not use the array names (student, adult, child, elderly) as variable names in the same DATA step.

Re: How to use array to create a lookup table?

learnin — Mon, 22 Apr 2019 15:06:16 GMT

So much thanks for your reply. But I want to ask what is the meaning in the code "set have"

Re: How to use array to create a lookup table?

learnin — Mon, 22 Apr 2019 15:11:16 GMT

Beside I would like to know the logic on how you input the fare from 1 srt_id to 91 dest_id. Should we use the nested do loop instead? Sorry for that, I am a new learner on SAS

Re: How to use array to create a lookup table?

Astounding — Mon, 22 Apr 2019 15:18:36 GMT

There is a DO loop that says:

do until (done);

That loop accomplishes both of the things you are asking about.

The SET statement (set have; ) refers to the data set that you have that contains all the destinations and prices. Each time through the loop executes the SET statement to read the next observation from your source of prices.
Within the loop, after the SET statement executes, there are four assignment statements that each populate one element of an array.

Re: How to use array to create a lookup table?

learnin — Mon, 22 Apr 2019 15:29:09 GMT

I am sorry that Code didn't work as it pointed out an error " Array subscript out of range at line 164 column 47" When I referring to the row 164, it had nothing special on the data.

Re: How to use array to create a lookup table?

Astounding — Mon, 22 Apr 2019 15:50:42 GMT

I set the dimensions of the arrays at 91,91 because those were the largest values that appear in your sample data. If you actually have larger values, you have to increase the array dimensions accordingly.

There is no substitute for knowing what is in your data.

If you are doing homework on arrays, there is no substitute for learning how arrays work. You took a good first step by finding and posting the error message. Next step: understand what the error message means.