Help using Base SAS procedures

new user trying to find the right lookup function

Reply
N/A
Posts: 0

new user trying to find the right lookup function

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
Super Contributor
Super Contributor
Posts: 3,174

Re: new user trying to find the right lookup function

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.
PROC Star
Posts: 1,562

Re: new user trying to find the right lookup function

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.
N/A
Posts: 0

Re: new user trying to find the right lookup function

(Double-Post Deleted)
N/A
Posts: 0

Re: new user trying to find the right lookup function

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.
Respected Advisor
Posts: 3,894

Re: new user trying to find the right lookup function

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!)
Super Contributor
Posts: 359

Re: new user trying to find the right lookup function

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;
Ask a Question
Discussion stats
  • 6 replies
  • 146 views
  • 0 likes
  • 5 in conversation