data set extraction from 2 files

[stellapersis7]

Hi all,

I have a dataset "MD1_CASE1" which has start (met_start) and end (met_end) date of drug called 'met'. the fill dates before, inbetween and after these start and end dates are there in another dataset called 'MD_GAPS_MET2   '. I need all the fill dates which are between met_start and met_end.

data WORK.MD1_CASE1;
infile datalines dsd truncover;
input study_id:32. SGA_START:MMDDYY10. MET_START:MMDDYY10. MET_END:MMDDYY10. persistance:32. cc:32.;
format SGA_START MMDDYY10. MET_START MMDDYY10. MET_END MMDDYY10.;
label study_id="Enrollee ID";
datalines;
20030373948 04/06/2017 04/13/2017 06/30/2017 180 1
20048044091 01/23/2018 01/31/2019 04/04/2019 180 1
20019834271 04/10/2018 04/16/2018 06/21/2018 180 1
20043148283 01/03/2018 07/05/2018 12/06/2019 180 1
20039989839 04/10/2017 03/21/2019 08/08/2019 180 1
20038790445 05/14/2017 09/02/2018 09/25/2019 148 1
20041462934 01/02/2018 11/26/2018 12/30/2019 180 1
20040616173 08/07/2017 04/02/2018 07/08/2019 180 1
20043946189 09/05/2017 04/06/2018 05/09/2018 180 1
20012920904 01/19/2018 10/10/2018 06/11/2019 180 1
;;;;

data WORK.MD_GAPS_MET2;

  infile datalines dsd truncover;

  input ENROLID:32. SVCDATE:MMDDYY10.;

  format SVCDATE MMDDYY10.;

  label ENROLID="Enrollee ID" SVCDATE="Date Service Incurred";

datalines;

20000003364 09/28/2017

20000003364 10/21/2017

20000003364 01/02/2018

20000003364 01/25/2018

20000003860 11/28/2017

20000003860 01/18/2018

20000003860 02/12/2018

20000003860 05/16/2018

20000003860 06/11/2018

20000003860 07/11/2018

;;;;

Data I have
dataset MD1_CASE1
Enroll id	met_start	Met_end
1	7/1/17	8/1/18
2	3/1/17	12/1/17
dataset MD_GAPS_MET2
Enroll id	fill date
1	6/10/17
	7/1/17
	4/1/18
	5/20/18
	8/1/18
	10/3/18
2	2/1/17
	3/1/17
	4/20/17
	7/18/17
	12/1/17
	12/4/17
data I want
Enroll id	filldate
1	7/1/17
	4/1/18
	5/20/18
	8/1/18
2	3/1/17
	4/20/17
	7/18/17
	12/1/17

Appreciate any help with sas code.

Thank you

[ballardw · Accepted solution]

You don't tell us which id variable you actually want. Showing examples with the same label for different variables, Study_id or Enrolid  (and neither of the labels you show is what you have the data step code shown) is just a bit confusing. Please use VARIABLES for wanted results.

It also is helpful to discuss things in general in terms of Variable names. You say "fill date" but do not clearly state if that value is actually the SVCDATE variable. The label says "Date Service Incurred" which leaves the question of is this actually a "fill date" or not.

Critical failure in your example data: there are no examples where the Study_id is the same as the Enrolid so we can't provide much in the way of tested code that works with your example data.

It would also be a good idea to describe the desired output for IDs in the MD1_Case1 data set that have no matches in the other data set.

With some assumptions:

proc sql ;
   create table work.want as
   select a.study_id, a.met_start, a.met_end
          ,b.svcdate
   from WORK.MD1_CASE1 as A
        left join
        WORK.MD_GAPS_MET2 as B
        on a.study_id = b.enrolid
   where a.met_start le b.svcdate le a.met_end
   ;
quit;

I leave in the start and end dates because it is quite possible that a single Svcdate could fit more than one start/end pair and this shows which end periods a particular result matches. This code will also have all of the Studyid, start/end dates even if there is no match because you did not provide any rule of what to do in that case (or data that we could even test with)