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;
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)
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”.
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)
Thank You ! the logic works perfectly
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.