Hello All,
I need help here, I have an example member table and a claims table. the requirement is I need all claims join would be on the member and all dates in between and including the earliest date and last date.
Please let me know if the requirement is clear...
Member Table | Claims Table | ||||||||||
Mbr_id | Earliest date | Last date | Mbr_id | Clm_num | Serive Start_date | Service_end_date | Mbr_NM | Mbr_lastNM | |||
90001199 | 6/30/2022 | 6/30/2022 | 90001199 | 7002378765 | 6/30/2022 | 6/30/2022 | ABV | lion | |||
90001191 | 1/3/2022 | 8/4/2022 | 90001199 | 7007978765 | 6/15/2022 | 6/30/2022 | ABV | lion | |||
90001192 | 9/30/2022 | 9/30/2022 | 90001191 | 7112378765 | 1/3/2022 | 6/30/2022 | ABV | lion | |||
90001193 | 1/1/2022 | 11/2/2022 | 90001191 | 9872378765 | 4/3/2022 | 6/4/2022 | ABV | lion | |||
90001194 | 3/2/2022 | 7/23/2022 | 90001192 | 1987678765 | 9/30/2022 | 9/30/2022 | ABV | lion | |||
90001195 | 6/3/2022 | 1/14/2022 | 90001192 | 7008753765 | 4/30/2022 | 10/30/2022 | ABV | lion | |||
90001196 | 3/27/2022 | 7/25/2022 | 90001193 | 5982378765 | 1/1/2022 | 11/2/2022 | ABV | lion | |||
90001197 | 3/5/2022 | 3/5/2022 | 90001193 | 9835678765 | 4/7/2022 | 10/2/2022 | ABV | lion | |||
90001193 | 7919378765 | 1/1/2022 | 7/18/2022 | ABV | lion | ||||||
90001194 | 7002378765 | 3/2/2022 | 7/23/2022 | ABV | lion | ||||||
90001195 | 7002378765 | 6/3/2022 | 1/14/2022 | ABV | lion | ||||||
90001196 | 7002378765 | 3/27/2022 | 7/25/2022 | ABV | lion | ||||||
90001197 | 7002378765 | 3/5/2022 | 3/5/2022 | ABV | lion |
Not sure what you mean when describing the join. If you are trying to find earliest claim start date and last claim end date for each member, the code would look something like this:
/* Create the sample data */
data CLAIMS;
infile datalines dsd truncover;
input Mbr_id:8. Clm_num:10. Service_Start_date:MMDDYY10. Service_end_date:MMDDYY10. Mbr_NM:$3. Mbr_lastNM:$4.;
format Mbr_id 8. Clm_num 10. Service_Start_date MMDDYY10. Service_end_date MMDDYY10.;
label Mbr_id="Member ID" Clm_num="Clm_num" Service_Start_date="Service Start date" Service_End_date="Service end date" ;
datalines4;
90001199,7002378765,06/30/2022,06/30/2022,ABV,lion
90001199,7007978765,06/15/2022,06/30/2022,ABV,lion
90001191,7112378765,01/03/2022,06/30/2022,ABV,lion
90001191,9872378765,04/03/2022,06/04/2022,ABV,lion
90001192,1987678765,09/30/2022,09/30/2022,ABV,lion
90001192,7008753765,04/30/2022,10/30/2022,ABV,lion
90001193,5982378765,01/01/2022,11/02/2022,ABV,lion
90001193,9835678765,04/07/2022,10/02/2022,ABV,lion
90001193,7919378765,01/01/2022,07/18/2022,ABV,lion
90001194,7002378765,03/02/2022,07/23/2022,ABV,lion
90001195,7002378765,06/03/2022,01/14/2022,ABV,lion
90001196,7002378765,03/27/2022,07/25/2022,ABV,lion
90001197,7002378765,03/05/2022,03/05/2022,ABV,lion
;;;;
data MEMBER;
infile datalines dsd truncover;
input Mbr_id:8. Earliest_date:MMDDYY10. Last_date:MMDDYY10.;
format Mbr_id 8. Earliest_date MMDDYY10. Last_date MMDDYY10.;
label Mbr_id="Member ID" Earliest_date="Earliest_date" Last_date="Last_date";
datalines4;
90001199,06/30/2022,06/30/2022
90001191,01/03/2022,08/04/2022
90001192,09/30/2022,09/30/2022
90001193,01/01/2022,11/02/2022
90001194,03/02/2022,07/23/2022
90001195,06/03/2022,01/14/2022
90001196,03/27/2022,07/25/2022
90001197,03/05/2022,03/05/2022
;;;;
/* Produce the report */
proc sql;
select m.mbr_id
,min(c.Service_Start_Date) format=mmddyy10. as Earliest_Start
,max(c.Service_End_Date) format=mmddyy10. as Last_End
from claims as c
inner join
member as m
on m.mbr_id=c.mbr_id
group by m.mbr_id
order by m.mbr_id
;
quit;
Which produces this report:
Member ID | Earliest_Start | Last_End |
---|---|---|
90001191 | 01/03/2022 | 06/30/2022 |
90001192 | 04/30/2022 | 10/30/2022 |
90001193 | 01/01/2022 | 11/02/2022 |
90001194 | 03/02/2022 | 07/23/2022 |
90001195 | 06/03/2022 | 01/14/2022 |
90001196 | 03/27/2022 | 07/25/2022 |
90001197 | 03/05/2022 | 03/05/2022 |
90001199 | 06/15/2022 |
06/30/2022 |
Is that enough to get you started?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: