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.
Please provide a sample data and desired output.
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.
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.
example data and calculation in excel attached.
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)".
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.