Real time scenarios -- need Help

[saskishore]

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

[SASJedi]

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?