BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
ChrisNZ
Tourmaline | Level 20
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.
deleted_user
Not applicable
(Double-Post Deleted)
deleted_user
Not applicable
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.
Patrick
Opal | Level 21
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!)
Flip
Fluorite | Level 6
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 737 views
  • 0 likes
  • 5 in conversation