Proc sql conditional join

[cho16]

Hi Team,

I have a first dataset that has the member number, groupnum , benpkg and other fields.

In the second table also i have the group_id, benpkg and other fields ( seperated as two tables based on the group_id  as EP_IN and second table as EP_NOTIN)

We need to use the below condition:

1. If group_ID includes “EP”, ie like “EP$”, which means that it’s a EP plan, then we need to join on both grpnum= names.group_ID and benpkg=names.Benefit_Package_ID, both grpnum and benpkg are in the members tables

2. If it’s not a EP plan, then we only need to join on  grpnum= names.group_Id.

3. Please use left join, so that we will still keep the records if there is no matching at names table

My logic:

proc sql ;

create table new_members_detail_fl_3 as

select M.*,E.Benefit_Package_Name,E.Group_ID

from new_members_detail_fl_2 M

left join EP_IN E

on M.grpnum=E.group_ID and M.benpkg=E.Benefit_Package_ID;

quit;

proc sql ;

create table new_members_detail_fl_4 as

select M.*, E.Benefit_Package_Name,E.Group_ID

from new_members_detail_fl_2 M

left join EP_NOTIN E

on M.grPnum=E.group_ID;

Can you please let me know how to implement this join condition in the single data step instead of twice.

quit;

[ChrisNZ]

One way to achieve such a one-step join is to load the second data set in 2 different hash tables, one with one key and one with 2 keys.
And call the find function corresponding to the GROUP_ID  (or maybe can you just use 2 keys but one is empty?)

Another way is to run a SQL query that contain 2 left join like yours and a union between the joins. The first join would be for group_ID includes “EP”  and the second one for group_ID doesn't include “EP”.

[PGStats · Accepted solution]

Seems like you want:

proc sql;
create table new_members_detail_fl_4 as
select 
	M.*,
	coalesce(E.Benefit_Package_Name, F.Benefit_Package_Name) as Benefit_Package_Name,
	coalesce(E.Group_ID, F.Group_ID) as Group_ID
 from 
	new_members_detail_fl_2 as M left join 
	(select * from EP_IN  as E where Group_ID eqt "EP") on M.grpNum=E.Group_ID and M.benpkg=E.Benefit_Package_ID left join
	(select * from EP_NOTIN as F where Group_ID net "EP") on M.grpNum=F.Group_ID;
quit;

(untested)

[cho16]

Thank You ! the logic works perfectly