topic Left join same table twice in SAS Programming

Left join same table twice

HeatherNewton — Wed, 26 Oct 2022 08:38:09 GMT

Possible to left join same table twice on different variables? What is the significance of this combination?

Proc sql;
Create table staging.baselii_boo as
Select a.*,
b.BOOUK as ID_TYPE_BOO,
c.BOOUK as BR_ID_TYPE_BOO
from staging.baselii_boo a
left join staging.boouk_id_type_mapping b on a.AMNA_USER_6_1=b.CMS
left join staging.boouk_id_type mapping c on a.BR_nid_type=c.CMS;
quit;

Re: Left join same table twice

Kurt_Bremser — Wed, 26 Oct 2022 09:55:15 GMT

It's a lookup for two different variables, using the same lookup table.

Simple technique, often used. But you can make it (much) faster today by loading the lookup table into a hash object and using the two variables as key in two FIND() method calls. Before the advent of the hash, we would often create a format from the lookup table and apply that twice.