DATA Step, Macro, Functions and more

Proc sql conditional join

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Proc sql conditional join

[ Edited ]

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;


Accepted Solutions
Solution
‎07-13-2016 10:25 PM
Respected Advisor
Posts: 4,655

Re: Proc sql conditional join

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)

PG

View solution in original post


All Replies
PROC Star
Posts: 1,564

Re: Proc sql conditional join

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”.

Solution
‎07-13-2016 10:25 PM
Respected Advisor
Posts: 4,655

Re: Proc sql conditional join

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)

PG
Contributor
Posts: 30

Re: Proc sql conditional join

Thank You ! the logic works perfectly

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1412 views
  • 0 likes
  • 3 in conversation