topic Re: lookup within an array in SAS Programming

lookup within an array

iamthejvd — Fri, 06 May 2016 14:23:51 GMT

Doing an array and I want the formula in the array to be a lookup, something like this:

data table.population;
set table.population;

Array income_(n) income_1 - income _100;

Array age_(n) age_1-age_100;

Do n=1 to 100;

if n=1 then age_(n) = current_age;

else age_(n) = age_(n-1)+1;

end;

Do n=1 to 100;

income_(n) = lookup(age_(n), table.age_by_income, Income);

end;

the lookup would be somthing like a vlookup in excel, returning the income based on the age array.

Re: lookup within an array

stat_sas — Fri, 06 May 2016 14:51:08 GMT

Please provide a sample data and desired output.

Re: lookup within an array

ballardw — Fri, 06 May 2016 14:57:16 GMT

I think you are looking for the WHICHN (or WHICHC if using character variables).

Whichn returns the position in an array that matches a specified value.

For instance

x = whichn(23, of age_(*)); Would return the which element of the array Age_ has the value of 23 or a 0 if not found.

So if I wanted to find an associated value I might use

y = Income (whichn(23,of age_(*))); to find the value in the income array that is in the same position as 23 in the age array.

I have no idea what you meand by table.age_by_Income. If you are intending to use a different dataset then that will not work at all in the manner you think. It may be possible in Proc Sql.

You may need to provide a more concrete example, with values for variables of your input and expected output. If you want code tested provide the data in the form of a datastep that reads a datalines or cards data section.

Re: lookup within an array

FreelanceReinh — Fri, 06 May 2016 15:12:23 GMT

Hi @iamthejvd,

Most probably, you can define a numeric informat, say INC_AGE, from the table "age_by_income" and then define income_(n) like this:

income_(n) = input(put(age_(n), 3.), inc_age.);

If table "age_by_income" contains age groups (such as "20-24", "25-30" etc.), format 3. in the above code will be replaced by a user-defined format. For more specific advice we would need to know what "age_by_income" looks like.

Re: lookup within an array

iamthejvd — Fri, 06 May 2016 15:40:12 GMT

example data and calculation in excel attached.

Re: lookup within an array

FreelanceReinh — Fri, 06 May 2016 16:12:33 GMT

Thanks for providing the data. You can create the informat I mentioned with the following code:

data inc_age;
infile cards eof=last;
retain fmtname '@inc_age';
input start label;
output;
return;
last: 
  hlo='O';
  label=.;
  output;
cards; 
30 7.50
31 13.53
32 8.66
33 11.13
34 14.46
35 15.73
36 13.01
37 16.24
38 6.78
39 12.30
40 16.26
41 14.46
42 15.73
43 13.01
;

proc format cntlin=inc_age;
run;

Then you can use the assignment statement from my previous post without any change:

income_(n) = input(put(age_(n), 3.), inc_age.);

Please make sure to fix your ARRAY statements by deleting the "(n)".