How to write SQL join with derived joinkey efficiently

[DucatiJong]

Hi,

When I performs an SQL join with a derived join key, I separates the step into proc sql. One to drive the joinkey and another to actually perform the join. I wonder if someone know of a more efficient way to do this. A simple example below to illustrate,

proc sql;

  create view prep_table as

  select * ,put(raw_var, $someformat.) as joinkey

  from main_table;

  create table output_table as

  select a.var ,b.var2

  from prep_table as a

    left join sub_table as b

      on a.joinkey = b.joinkey;

  quit;  

[kiranv_ · Accepted solution]

but what is point to make another column in the view, unless your building a index or something useful. You can do pretty much in same step as 

  create table output_table as

  select a.var ,b.var2

  from prep_table as a

    left join sub_table as b

      on put(a.raw_var, $someformat.) = b.joinkey;

  quit;  

[DucatiJong]

Thanks