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
– Robert Penridge, Commented Sep 24, 2015 at 14:59
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
– Tomek P, Commented Sep 24, 2015 at 15:02
Sounds like a hash table may be the 'standard' way to go then. — Robert Penridge
– Robert Penridge, Commented Sep 24, 2015 at 15:04

data _null_ · Accepted Answer · 2015-09-24 16:01:39Z

2

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;

edited Sep 24, 2015 at 16:01

answered Sep 24, 2015 at 15:04

data _null_

9,12914 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tomek P Over a year ago

perfect, that's exactly what I need :) and an interesting piece of code for the lookup table. Definitely need to look up the hash tables functionality.

Collectives™ on Stack Overflow

sas load array from table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related