topic How to write SQL join with derived joinkey efficiently in New SAS User

How to write SQL join with derived joinkey efficiently

DucatiJong — Mon, 05 Nov 2018 20:04:09 GMT

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;

Re: How to write SQL join with derived joinkey efficiently

kiranv_ — Mon, 05 Nov 2018 21:20:09 GMT

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;

Re: How to write SQL join with derived joinkey efficiently

DucatiJong — Mon, 05 Nov 2018 22:05:42 GMT

Thanks