topic Re: new user trying to find the right lookup function in SAS Procedures

new user trying to find the right lookup function

deleted_user — Thu, 08 Oct 2009 18:02:54 GMT

I am currently trying to write a SAS program to mimic a project I just did in Excel using the Vlookup function. I have a table with the following variables (simplified):

State Year Taxrate AdjacentState AdjacentTax

I also made a variable which combines the state and year, in order to have an unique identifier for each row.

I have values for the first four variables. The fourth has to reference the tax rate from the adjacent state in the same year. This is where I am stuck. Is there a procedure in SAS which will do this, like VLookup in excel? And if not, should I just try to use inelegant SQL table joins instead?

Thanks for the assistance

Re: new user trying to find the right lookup function

sbb — Thu, 08 Oct 2009 18:16:13 GMT

With a DATA step approach, you use a PUT function in an assignment statement. The second argument in the PUT function references a SAS FORMAT. You will need to use PROC FORMAT to build your look-up argument (START and possibly an END) data-string and a "LABEL" which SAS returns in the assignment statement, similar to VLOOKUP.

Scott Barry
SBBWorks, Inc.

Re: new user trying to find the right lookup function

ChrisNZ — Tue, 13 Oct 2009 03:34:22 GMT

Extending Scott's post, to show you an example of syntax:

formats:

'2009AK' = '0.20'

'2008AK' = '0.25'

data step:

RATE=put(YEAR ||STATE, $rate.);

Note: If you have a numeric YEAR and want a numeric RATE, this becomes:

RATE=input( put( put(YEAR,4.) ||STATE, $rate.) ,32.);

^
^
^

|
|
The bit in italics converts year to a string

|
|

|
This bit applies the format

|

|

The bit in bold converts the rate from a string to a number

Another solution is to use hash tables, which can lookup values using several keys, without having to concatenate keys into a single key. Probably when you are a bit more familiar with sas though. Familiarise yourself with formats, and put hash tables on your radar.

Re: new user trying to find the right lookup function

deleted_user — Tue, 13 Oct 2009 04:06:37 GMT

(Double-Post Deleted)

Re: new user trying to find the right lookup function

deleted_user — Tue, 13 Oct 2009 04:13:47 GMT

Thank you for the further clarification. I looked into proc format, but it seems that I have to specify all the formats in advance, and in this instance I'd have to list the tax rate for each state/year combination in the code. This could work once, but I would want something that can process additional tables with new tax rates. I would want the format to assign a tax rate to each state/year combination based on the table, then in the data step, apply the format to the AdjacentState variable so the AdjacentTaxRate is returned. So for the format, it might be:

proc format;
value $rate
stateyear = taxrate;

rather than

'2009AK' = '0.20'

then for the data step, it would be the same, with AdjacentTaxRate=put(YEAR||STATE, $rate)

Is that close to what I should be doing?

I really appreciate the input.

Re: new user trying to find the right lookup function

Patrick — Tue, 13 Oct 2009 07:54:56 GMT

Have one more look at Proc Format and there at cntlin and cntlout

... or use simple SQL syntax:

data taxrates;
Year=2008; State='AK'; Taxrate=0.25; output;
Year=2009; State='AK'; Taxrate=0.35; output;
Year=2009; State='NY'; Taxrate=0.30; output;
run;

data have;
State='AK'; Year=2009; Taxrate='0.35'; AdjacentState='NY'; output;
run;

proc sql;
create table want as
select h.state, h.year, h.taxrate, h.AdjacentState,t.taxrate format=4.2 as AdjacentTax
from have as h left join taxrates as t
on h.year=t.year and h.AdjacentState=t.state;
quit;

... or use a hash object:
http://www2.sas.com/proceedings/sugi29/238-29.pdf
(article by one of my absolute favourites in the SAS field - advanced!)

Re: new user trying to find the right lookup function

Flip — Wed, 14 Oct 2009 12:31:28 GMT

Try this. It creates a lookup table and adds the ATaxrate to the base table based on the index.

data taxrates( index = (stidx = (year Adjacentstate)));
Year=2008; AdjacentState='AK'; ATaxrate=0.25; output;
Year=2009; AdjacentState='AK'; ATaxrate=0.35; output;
Year=2009; AdjacentState='NY'; ATaxrate=0.30; output;
run;

data have;
State='AK'; Year=2009; Taxrate='0.35'; AdjacentState='NY'; output;
run;

data have2;
set have;
set taxrates key = stidx / unique;
if _iorc_>0 then ATaxrate = . ;
run;