topic Proc sql conditional join in SAS Programming

Proc sql conditional join

cho16 — Wed, 13 Jul 2016 21:42:58 GMT

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;

Re: Proc sql conditional join

ChrisNZ — Wed, 13 Jul 2016 21:52:55 GMT

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

Re: Proc sql conditional join

PGStats — Wed, 13 Jul 2016 22:14:26 GMT

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)

Re: Proc sql conditional join

cho16 — Thu, 14 Jul 2016 02:25:25 GMT

Thank You ! the logic works perfectly