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?
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.
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.
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:
stateyear = taxrate;
'2009AK' = '0.20'
then for the data step, it would be the same, with AdjacentTaxRate=put(YEAR||STATE, $rate)
State='AK'; Year=2009; Taxrate='0.35'; AdjacentState='NY'; output;
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;