BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cho16
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

3 REPLIES 3
ChrisNZ
Tourmaline | Level 20

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
Opal | Level 21

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
cho16
Obsidian | Level 7

Thank You ! the logic works perfectly

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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