sas load array from table

Question

I need to load a 2d array from table to a SAS data step and then use it to perform variable transformation in another table. The array looks something like this:

rows: initial_value 1-4
columns: year 1-4

1 1 2 3 2 2 2 3 3 4 4 4 4 4 5 6

So far I've hardcoded the array into sas code: data example; array lookup {4,4} _temporary_ 1 1 2 3 2 2 2 3 3 4 4 4 4 4 5 6 ; set source(keep=(value years)); value_final = lookup{value, years}; run;

How do I do the same thing without hardcoding the matrix, but rather loading a table into data step and using as array?

If you are working with matrices then you may want to look into proc iml. Rick Wicklin has some great examples on his blog (blogs.sas.com/content/iml). Alternatively, look into hash tables. On a side note, you may want to describe exactly what you plan on doing transformation-wise (so people can be more helpful), or simplify your question to just how can I load a table into a multidimensional array. — Robert Penridge
thanks, this is a rather anecdoctical case - I only need the array for a simple lookup. One solution I've just come up with would be to transform the array to the form of list - value for each pair of year and initial value and then make a join. Might be answering my own question, but I'd still be curious if it's possible to load an array from a table in a data step :) — Tomek P
Sounds like a hash table may be the 'standard' way to go then. — Robert Penridge

data _null_ data _null_ · Accepted Answer · 2015-09-24T15:04:14

I would put the lookup table into a data set with years and value. Then load that into and associative array.

data lookup;
   do value = 1 to 4;
      do years = 1 to 4;
         input value_final @;
         output;
         end;
      end;
   cards;
1 1 2 3 
2 2 2 3 
3 4 4 4 
4 4 5 6
;;;;
   run;
proc print;
   run;
data source;
   input years value @@;
   cards;
3 2  4 1 5 0  2 2
;;;;
   run;

data example;
   if _n_ eq 1 then do;
      if 0 then set lookup;
      declare hash lookup(dataset:'lookup');
      lookup.definekey('value','years');
      lookup.definedata('value_final');
      lookup.definedone();
      end;
   set source;
   if lookup.find() ne 0 then call missing(value_final);
   run;
proc print;
   run;

You can load into array but it is a bit clunky and you have to know the dimension. And you have to check for subscript out of range which I did not do.

data lookup;
   do value = 1 to 4;
      do years = 1 to 4;
         input value_final @;
         output;
         end;
      end;
   cards;
1 1 2 3 
2 2 2 3 
3 4 4 4 
4 4 5 6
;;;;
   run;
proc print;
   run;
data source;
   input years value @@;
   cards;
3 2  4 1 2 2
;;;;
   run;

data example;
   array lookup[4,4] _temporary_;
   if _n_ eq 1 then do;
      do while(not eof);
         set lookup end=eof;
         lookup[value,years]=value_final;
         end;
      end;
   set source;
   value_final=lookup[value,years];
   run;
proc print;
   run;

sas load array from table

1 Answers