PROC SQL: Join records in table 2 whose DATETIME is between two DATETIME variables in Table 1?

[PharmlyDoc]

Background:

1. I have a table named CONTACT_DATES which contains patients and two contact dates spanning 1 year (duplicate records are purposeful). 

2. I have an ORDER_DATES table that contains medication orders, which contains the PAT_ID, ORDER_ID, and ORDERING_DATE.

3. I have a DISPENSE_DATES table that contains the PAT_ID, DISP_REF_ID, and DISPENSE_DATE.

I want:

1. To join the order records (on PAT_ID) from ORDER_DATES to CONTACT_DATES where the ORDERING_DATE is between CONTACT_DATE1 AND CONTACT_DATE2.

2. If a patient has an order record is between CONTACT_DATE1 AND CONTACT_DATE2, then join the dispense records from DISPENSE_DATES to CONTACT_DATES where the DISPENSE_DATE is between CONTACT_DATE1 AND CONTACT_DATE2.

Using proc sql, is there a better method than what I've done? 

Thanks.

data CONTACT_DATES;
infile datalines delimiter=',';
input PAT_ID $ CONTACT_DATE1 ANYDTDTM19. CONTACT_DATE2 ANYDTDTM19.;
format CONTACT_DATE1 DATETIME19. CONTACT_DATE2 DATETIME19.;
datalines;
904900,21Dec2013:00:00:00,21Dec2014:00:00:00,
904900,21Dec2013:00:00:00,21Dec2014:00:00:00,
904900,21Feb2015:00:00:00,21Feb2016:00:00:00,
904900,21Feb2015:00:00:00,21Feb2016:00:00:00,
904900,08Jul2020:00:00:00,08Jul2021:00:00:00,
904901,21Dec2013:00:00:00,21Dec2014:00:00:00,
904901,08Jul2020:00:00:00,08Jul2021:00:00:00,
904902,21Dec2013:00:00:00,21Dec2014:00:00:00,
904902,08Jul2020:00:00:00,08Jul2021:00:00:00,
904903,22Feb2013:00:00:00,22Feb2014:00:00:00,
904903,10Jan2014:00:00:00,10Jan2015:00:00:00,
904903,19Mar2014:00:00:00,19Mar2015:00:00:00,
904903,08Jul2020:00:00:00,08Jul2021:00:00:00,
904904,21Dec2013:00:00:00,21Dec2014:00:00:00,
904904,22Feb2014:00:00:00,22Feb2015:00:00:00,
904904,08Jul2020:00:00:00,08Jul2021:00:00:00,
904905,22Feb2014:00:00:00,22Feb2015:00:00:00,
904905,19Mar2014:00:00:00,19Mar2015:00:00:00,
904905,08Jul2020:00:00:00,08Jul2021:00:00:00,
904906,21Dec2013:00:00:00,21Dec2014:00:00:00,
904906,08Jul2020:00:00:00,08Jul2021:00:00:00,
904907,22Feb2014:00:00:00,22Feb2015:00:00:00,
904907,19Mar2014:00:00:00,19Mar2015:00:00:00,
904907,08Jul2020:00:00:00,08Jul2021:00:00:00,
904908,22Feb2014:00:00:00,22Feb2015:00:00:00,
904908,19Mar2014:00:00:00,19Mar2015:00:00:00,
904908,08Jul2020:00:00:00,08Jul2021:00:00:00,
904909,22Feb2014:00:00:00,22Feb2015:00:00:00,
904909,28Feb2014:00:00:00,28Feb2015:00:00:00,
904909,08Jul2020:00:00:00,08Jul2021:00:00:00,
904910,20Dec2013:00:00:00,20Dec2014:00:00:00,
904910,22Feb2014:00:00:00,22Feb2015:00:00:00,
904910,08Jul2020:00:00:00,08Jul2021:00:00:00,
904911,20Jan2014:00:00:00,20Jan2015:00:00:00,
904911,22Feb2014:00:00:00,22Feb2015:00:00:00,
904911,08Jul2020:00:00:00,08Jul2021:00:00:00,
904912,22Feb2014:00:00:00,22Feb2015:00:00:00,
904912,21Dec2014:00:00:00,21Dec2015:00:00:00,
904912,08Jul2020:00:00:00,08Jul2021:00:00:00,
904913,21Dec2013:00:00:00,21Dec2014:00:00:00,
904913,22Feb2014:00:00:00,22Feb2015:00:00:00,
904913,20Mar2014:00:00:00,20Mar2015:00:00:00,
904913,08Jul2020:00:00:00,08Jul2021:00:00:00,
904914,21Dec2012:00:00:00,21Dec2013:00:00:00,
904914,22Feb2014:00:00:00,22Feb2015:00:00:00,
904914,18Mar2014:00:00:00,18Mar2015:00:00:00,
904914,08Jul2020:00:00:00,08Jul2021:00:00:00,
904915,21Jan2014:00:00:00,21Jan2015:00:00:00,
904915,19Feb2014:00:00:00,19Feb2015:00:00:00,
904915,08Jul2020:00:00:00,08Jul2021:00:00:00,
904916,28Dec2013:00:00:00,28Dec2014:00:00:00,
904916,22Feb2014:00:00:00,22Feb2015:00:00:00,
904916,12Mar2014:00:00:00,12Mar2015:00:00:00,
904916,08Jul2020:00:00:00,08Jul2021:00:00:00,
904917,01Jan2012:00:00:00,01Jan2013:00:00:00,
904917,22Feb2014:00:00:00,22Feb2015:00:00:00,
904917,19Mar2014:00:00:00,19Mar2015:00:00:00,
904917,08Jul2020:00:00:00,08Jul2021:00:00:00,
904918,21Dec2013:00:00:00,21Dec2014:00:00:00,
904918,22Feb2014:00:00:00,22Feb2015:00:00:00,
904918,08Jul2020:00:00:00,08Jul2021:00:00:00,
904919,19Dec2013:00:00:00,19Dec2014:00:00:00,
904919,22Feb2014:00:00:00,22Feb2015:00:00:00,
904919,05Mar2014:00:00:00,05Mar2015:00:00:00,
904919,21Mar2014:00:00:00,21Mar2015:00:00:00,
904919,08Jul2020:00:00:00,08Jul2021:00:00:00,
904920,21Dec2013:00:00:00,21Dec2014:00:00:00,
904920,21Dec2013:00:00:00,21Dec2014:00:00:00,
904920,21Feb2015:00:00:00,21Feb2016:00:00:00,
904920,21Feb2015:00:00:00,21Feb2016:00:00:00,
904920,08Jul2020:00:00:00,08Jul2021:00:00:00,
;
run;

data ORDER_DATES;
infile datalines delimiter=',';
input PAT_ID $ ORDER_ID 5. ORDERING_DATE ANYDTDTM19. ;
format ORDERING_DATE DATETIME19.;
datalines;
904903,51199,31Mar2020:00:00:00
904901,51200,31Mar2020:00:00:00
904901,51201,31Mar2020:00:00:00
904909,51202,31Mar2020:00:00:00
904911,51203,31Mar2020:00:00:00
904913,51204,31Mar2020:00:00:00
904915,51205,31Mar2020:00:00:00
904901,51206,31Mar2020:00:00:00
904905,51207,31Mar2020:00:00:00
904918,51208,31Mar2020:00:00:00
904910,51209,31Mar2020:00:00:00
904918,51210,31Mar2020:00:00:00
904914,51211,31Mar2020:00:00:00
904904,51212,31Mar2020:00:00:00
904902,51213,31Mar2020:00:00:00
904913,51214,31Mar2020:00:00:00
904916,51215,29Nov2018:00:00:00
904904,51216,29Nov2018:00:00:00
904912,51217,29Nov2018:00:00:00
904914,51218,07Jun2019:00:00:00
904906,51219,07Jun2019:00:00:00
904920,51220,07Jun2019:00:00:00
904902,51221,02Apr2020:00:00:00
904902,51222,02Apr2020:00:00:00
904918,51223,02Apr2020:00:00:00
904916,51224,02Apr2020:00:00:00
904915,51225,02Apr2020:00:00:00
904916,51226,02Apr2020:00:00:00
904905,51227,02Apr2020:00:00:00
904916,51228,25Jun2020:00:00:00
904912,51229,07Jul2021:00:00:00
904917,51230,07Jul2021:00:00:00
904919,51231,07Jul2021:00:00:00
904920,51232,29Jun2018:00:00:00
904917,51233,29Jun2018:00:00:00
904906,51234,29Jun2018:00:00:00
904913,51235,29Jun2018:00:00:00
904910,51236,06Jul2018:00:00:00
904916,51237,06Jul2018:00:00:00
904907,51238,06Jul2018:00:00:00
904908,51239,06Jul2018:00:00:00
904915,51240,06Jul2018:00:00:00
904919,51241,06Jul2018:00:00:00
904901,51242,04Oct2018:00:00:00
904907,51243,02Jul2019:00:00:00
904917,51244,02Jan2020:00:00:00
904906,51245,03Apr2020:00:00:00
904903,51246,03Apr2020:00:00:00
904917,51247,03Apr2020:00:00:00
904909,51248,20Oct2021:00:00:00
904907,51249,20Oct2021:00:00:00
904906,51250,12Sep2016:00:00:00
904905,51251,12Sep2016:00:00:00
904910,51252,12Sep2016:00:00:00
904920,51253,12Sep2016:00:00:00
904919,51254,19Sep2016:00:00:00
904919,51255,19Sep2016:00:00:00
904919,51256,19Sep2016:00:00:00
904920,51257,19Sep2016:00:00:00
904913,51258,19Sep2016:00:00:00
904911,51259,19Sep2016:00:00:00
904911,51260,18Dec2016:00:00:00
904904,51261,15Sep2017:00:00:00
904913,51262,18Mar2018:00:00:00
904913,51263,18Jun2018:00:00:00
904903,51264,18Jun2018:00:00:00
904914,51265,18Jun2018:00:00:00
904908,51266,04Jan2020:00:00:00
904911,51267,04Jan2020:00:00:00
904911,51268,03Apr2020:00:00:00
904906,51269,03Apr2020:00:00:00
904920,51270,03Apr2020:00:00:00
904916,51271,03Apr2020:00:00:00
904913,51272,03Apr2020:00:00:00
904902,51273,09Apr2021:00:00:00
904918,51274,09Apr2021:00:00:00
904913,51275,09Apr2021:00:00:00
904908,51276,09Apr2021:00:00:00
904906,51277,16Apr2021:00:00:00
904914,51278,16Apr2021:00:00:00
904907,51279,16Apr2021:00:00:00
904909,51280,16Apr2021:00:00:00
904908,51281,16Apr2021:00:00:00
904902,51282,16Apr2021:00:00:00
904919,51283,15Jul2021:00:00:00
904911,51284,12Mar2019:00:00:00
904907,51285,12Apr2020:00:00:00
904908,51286,08Oct2018:00:00:00
904918,51287,16Jan2020:00:00:00
904910,51288,16Jan2020:00:00:00
904920,51289,10Aug2016:00:00:00
904920,51290,10Aug2016:00:00:00
904914,51291,10Aug2016:00:00:00
904903,51292,10Aug2016:00:00:00
904910,51293,17Aug2016:00:00:00
904905,51294,17Aug2016:00:00:00
904915,51295,17Aug2016:00:00:00
904903,51296,17Aug2016:00:00:00
904903,51297,17Aug2016:00:00:00
904907,51298,15Nov2016:00:00:00
904903,51299,13Aug2017:00:00:00
904912,51300,16May2018:00:00:00
904905,51301,16May2018:00:00:00
904915,51302,16May2018:00:00:00
904919,51303,01Mar2020:00:00:00
904916,51304,01Mar2020:00:00:00
904910,51305,01Mar2020:00:00:00
904916,51306,01Mar2020:00:00:00
904908,51307,01Mar2020:00:00:00
904913,51308,01Mar2020:00:00:00
904918,51309,07Aug2016:00:00:00
904912,51310,07Aug2016:00:00:00
904914,51311,07Aug2016:00:00:00
904902,51312,07Aug2016:00:00:00
904907,51313,14Aug2016:00:00:00
904919,51314,14Aug2016:00:00:00
904916,51315,14Aug2016:00:00:00
904913,51316,14Aug2016:00:00:00
904912,51317,14Aug2016:00:00:00
904915,51318,12Nov2016:00:00:00
904900,51319,10Aug2017:00:00:00
904902,51320,13May2018:00:00:00
904911,51321,13May2018:00:00:00
904915,51322,13May2018:00:00:00
904918,51323,27Feb2020:00:00:00
904905,51324,27Feb2020:00:00:00
904920,51325,27Feb2020:00:00:00
904900,51326,27Feb2020:00:00:00
904909,51327,27Feb2020:00:00:00
904914,51328,27Feb2020:00:00:00
904914,51329,18May2020:00:00:00
904907,51330,26Mar2021:00:00:00
904920,51331,27Sep2017:00:00:00
904920,51332,27Sep2017:00:00:00
904919,51333,27Sep2017:00:00:00
904914,51334,05Apr2018:00:00:00
904908,51335,05Apr2018:00:00:00
904908,51336,05Apr2018:00:00:00
904905,51337,30Jan2019:00:00:00
904904,51338,30Jan2019:00:00:00
904918,51339,30Jan2019:00:00:00
904920,51340,30Jan2019:00:00:00
904907,51341,30Jan2019:00:00:00
904900,51342,30Jan2019:00:00:00
904905,51343,30Jan2019:00:00:00
904918,51344,24Apr2019:00:00:00
904919,51345,23May2019:00:00:00
904900,51346,25Jun2020:00:00:00
904908,51347,25Jun2020:00:00:00
904911,51348,25Jun2020:00:00:00
904914,51349,25Jun2020:00:00:00
904910,51350,25Jun2020:00:00:00
904904,51351,25Jun2020:00:00:00
904910,51352,25Jun2020:00:00:00
904904,51353,25Jun2020:00:00:00
904909,51354,25Jun2020:00:00:00
904918,51355,25Jun2020:00:00:00
904920,51356,25Jun2020:00:00:00
904910,51357,25Jun2020:00:00:00
904905,51358,25Jun2020:00:00:00
904916,51359,25Jun2020:00:00:00
904913,51360,25Jun2020:00:00:00
904917,51361,25Jun2020:00:00:00
904906,51362,25Jun2020:00:00:00
904911,51363,25Jun2020:00:00:00
904919,51364,25Jun2020:00:00:00
904917,51365,25Jun2020:00:00:00
904916,51366,25Jun2020:00:00:00
904919,51367,25Jun2020:00:00:00
904901,51368,25Jun2020:00:00:00
904909,51369,25Jun2020:00:00:00
904906,51370,25Jun2020:00:00:00
904919,51371,25Jun2020:00:00:00
904918,51372,25Jun2020:00:00:00
904912,51373,25Jun2020:00:00:00
904910,51374,25Jun2020:00:00:00
904912,51375,25Jun2020:00:00:00
904919,51376,25Jun2020:00:00:00
904912,51377,25Jun2020:00:00:00
904901,51378,25Jun2020:00:00:00
904915,51379,25Jun2020:00:00:00
904910,51380,25Jun2020:00:00:00
904912,51381,25Jun2020:00:00:00
904915,51382,25Jun2020:00:00:00
904918,51383,25Jun2020:00:00:00
904900,51384,25Jun2020:00:00:00
904907,51385,25Jun2020:00:00:00
904909,51386,25Jun2020:00:00:00
904914,51387,25Jun2020:00:00:00
904902,51388,25Jun2020:00:00:00
904906,51389,25Jun2020:00:00:00
904914,51390,25Jun2020:00:00:00
904909,51391,25Jun2020:00:00:00
904917,51392,25Jun2020:00:00:00
904916,51393,25Jun2020:00:00:00
904910,51394,25Jun2020:00:00:00
904901,51395,25Jun2020:00:00:00
904900,51396,25Jun2020:00:00:00
904907,51397,25Jun2020:00:00:00
904914,51398,25Jun2020:00:00:00
904903,51399,25Jun2020:00:00:00
904902,51400,25Jun2020:00:00:00
904902,51401,25Jun2020:00:00:00
904904,51402,25Jun2020:00:00:00
904913,51403,25Jun2020:00:00:00
904907,51404,25Jun2020:00:00:00
904913,51405,25Jun2020:00:00:00
904906,51406,25Jun2020:00:00:00
904915,51407,25Jun2020:00:00:00
904910,51408,25Jun2020:00:00:00
904906,51409,25Jun2020:00:00:00
904903,51410,25Jun2020:00:00:00
904907,51411,25Jun2020:00:00:00
904917,51412,25Jun2020:00:00:00
904906,51413,25Jun2020:00:00:00
904917,51414,25Jun2020:00:00:00
904918,51415,25Jun2020:00:00:00
904906,51416,25Jun2020:00:00:00
904909,51417,25Jun2020:00:00:00
904909,51418,25Jun2020:00:00:00
904902,51419,25Jun2020:00:00:00
904907,51420,25Jun2020:00:00:00
904914,51421,25Jun2020:00:00:00
904902,51422,25Jun2020:00:00:00
904917,51423,25Jun2020:00:00:00
904907,51424,25Jun2020:00:00:00
904902,51425,25Jun2020:00:00:00
904905,51426,25Jun2020:00:00:00
904914,51427,25Jun2020:00:00:00
904904,51428,25Jun2020:00:00:00
904902,51429,25Jun2020:00:00:00
904920,51430,25Jun2020:00:00:00
904915,51431,25Jun2020:00:00:00
904917,51432,25Jun2020:00:00:00
904913,51433,25Jun2020:00:00:00
904917,51434,25Jun2020:00:00:00
904906,51435,25Jun2020:00:00:00
904916,51436,25Jun2020:00:00:00
904904,51437,25Jun2020:00:00:00
904911,51438,25Jun2020:00:00:00
904910,51439,25Jun2020:00:00:00
904904,51440,25Jun2020:00:00:00
904916,51441,25Jun2020:00:00:00
904909,51442,25Jun2020:00:00:00
904920,51443,25Jun2020:00:00:00
904905,51444,25Jun2020:00:00:00
904913,51445,25Jun2020:00:00:00
904909,51446,25Jun2020:00:00:00
904920,51447,25Jun2020:00:00:00
904910,51448,25Jun2020:00:00:00
904911,51449,25Jun2020:00:00:00
904908,51450,25Jun2020:00:00:00
904920,51451,25Jun2020:00:00:00
904916,51452,25Jun2020:00:00:00
904913,51453,25Jun2020:00:00:00
904906,51454,25Jun2020:00:00:00
904903,51455,25Jun2020:00:00:00
904907,51456,25Jun2020:00:00:00
904908,51457,25Jun2020:00:00:00
904902,51458,25Jun2020:00:00:00
904902,51459,25Jun2020:00:00:00
904900,51460,25Jun2020:00:00:00
904912,51461,25Jun2020:00:00:00
904910,51462,25Jun2020:00:00:00
904907,51463,25Jun2020:00:00:00
904916,51464,25Jun2020:00:00:00
904920,51465,25Jun2020:00:00:00
904907,51466,25Jun2020:00:00:00
904909,51467,25Jun2020:00:00:00
904903,51468,25Jun2020:00:00:00
904901,51469,25Jun2020:00:00:00
904900,51470,25Jun2020:00:00:00
904919,51471,25Jun2020:00:00:00
904914,51472,25Jun2020:00:00:00
904904,51473,25Jun2020:00:00:00
904900,51474,25Jun2020:00:00:00
904918,51475,25Jun2020:00:00:00
904919,51476,25Jun2020:00:00:00
904900,51477,25Jun2020:00:00:00
904901,51478,25Jun2020:00:00:00
904906,51479,25Jun2020:00:00:00
904917,51480,25Jun2020:00:00:00
904904,51481,25Jun2020:00:00:00
904914,51482,25Jun2020:00:00:00
904906,51483,25Jun2020:00:00:00
904915,51484,25Jun2020:00:00:00
904907,51485,25Jun2020:00:00:00
904909,51486,25Jun2020:00:00:00
904902,51487,25Jun2020:00:00:00
904904,51488,25Jun2020:00:00:00
904915,51489,25Jun2020:00:00:00
904900,51490,25Jun2020:00:00:00
904902,51491,25Jun2020:00:00:00
904904,51492,25Jun2020:00:00:00
904915,51493,25Jun2020:00:00:00
904910,51494,25Jun2020:00:00:00
904901,51495,25Jun2020:00:00:00
904918,51496,25Jun2020:00:00:00
904903,51497,25Jun2020:00:00:00
904903,51498,25Jun2020:00:00:00
904907,51499,25Jun2020:00:00:00
904909,51500,25Jun2020:00:00:00
904915,51501,25Jun2020:00:00:00
904915,51502,25Jun2020:00:00:00
904901,51503,25Jun2020:00:00:00
904905,51504,25Jun2020:00:00:00
904900,51505,25Jun2020:00:00:00
904920,51506,25Jun2020:00:00:00
904908,51507,25Jun2020:00:00:00
904904,51508,25Jun2020:00:00:00
904906,51509,25Jun2020:00:00:00
904917,51510,25Jun2020:00:00:00
904903,51511,25Jun2020:00:00:00
904917,51512,25Jun2020:00:00:00
904919,51513,25Jun2020:00:00:00
904911,51514,25Jun2020:00:00:00
904910,51515,25Jun2020:00:00:00
904918,51516,25Jun2020:00:00:00
904903,51517,25Jun2020:00:00:00
904918,51518,25Jun2020:00:00:00
904917,51519,25Jun2020:00:00:00
904911,51520,25Jun2020:00:00:00
904903,51521,25Jun2020:00:00:00
904901,51522,25Jun2020:00:00:00
904903,51523,25Jun2020:00:00:00
904915,51524,25Jun2020:00:00:00
904907,51525,25Jun2020:00:00:00
904914,51526,25Jun2020:00:00:00
904904,51527,25Jun2020:00:00:00
904908,51528,25Jun2020:00:00:00
904907,51529,25Jun2020:00:00:00
904913,51530,25Jun2020:00:00:00
904906,51531,25Jun2020:00:00:00
904912,51532,25Jun2020:00:00:00
904910,51533,25Jun2020:00:00:00
904915,51534,25Jun2020:00:00:00
904905,51535,25Jun2020:00:00:00
904905,51536,25Jun2020:00:00:00
904920,51537,25Jun2020:00:00:00
904914,51538,16Nov2013:00:00:00
904909,51539,16Nov2013:00:00:00
904920,51540,16Nov2013:00:00:00
904917,51541,16Jan2014:00:00:00
904908,51542,16Jan2014:00:00:00
904907,51543,21Apr2014:00:00:00
904905,51544,21Apr2014:00:00:00
904900,51545,27Dec2015:00:00:00
904903,51546,27Dec2015:00:00:00
904901,51547,16Nov2013:00:00:00
904917,51548,16Nov2013:00:00:00
904916,51549,16Nov2013:00:00:00
904917,51550,16Jan2014:00:00:00
904907,51551,16Jan2014:00:00:00
904906,51552,21Apr2014:00:00:00
904903,51553,21Apr2014:00:00:00
904909,51554,27Dec2015:00:00:00
904901,51555,27Dec2015:00:00:00
904906,51556,16Nov2013:00:00:00
904917,51557,16Nov2013:00:00:00
904907,51558,16Nov2013:00:00:00
904904,51559,16Jan2014:00:00:00
904912,51560,16Jan2014:00:00:00
904901,51561,21Apr2014:00:00:00
904902,51562,21Apr2014:00:00:00
904920,51563,27Dec2015:00:00:00
904917,51564,27Dec2015:00:00:00
904903,51565,16Nov2013:00:00:00
904908,51566,16Nov2013:00:00:00
904919,51567,16Nov2013:00:00:00
904910,51568,16Jan2014:00:00:00
904901,51569,16Jan2014:00:00:00
904902,51570,21Apr2014:00:00:00
904911,51571,21Apr2014:00:00:00
904908,51572,27Dec2015:00:00:00
904920,51573,27Dec2015:00:00:00
904904,51574,16Nov2013:00:00:00
904918,51575,16Nov2013:00:00:00
904906,51576,16Nov2013:00:00:00
904907,51577,16Jan2014:00:00:00
904920,51578,16Jan2014:00:00:00
904916,51579,21Apr2014:00:00:00
904912,51580,21Apr2014:00:00:00
904900,51581,27Dec2015:00:00:00
904915,51582,27Dec2015:00:00:00
904917,51583,16Nov2013:00:00:00
904910,51584,16Nov2013:00:00:00
904920,51585,16Nov2013:00:00:00
904920,51586,16Jan2014:00:00:00
904907,51587,16Jan2014:00:00:00
904902,51588,21Apr2014:00:00:00
904917,51589,21Apr2014:00:00:00
904910,51590,27Dec2015:00:00:00
904909,51591,27Dec2015:00:00:00
904905,51592,16Nov2013:00:00:00
904900,51593,16Nov2013:00:00:00
904907,51594,16Nov2013:00:00:00
904907,51595,16Jan2014:00:00:00
904915,51596,16Jan2014:00:00:00
904911,51597,21Apr2014:00:00:00
904903,51598,21Apr2014:00:00:00
904909,51599,27Dec2015:00:00:00
904908,51600,27Dec2015:00:00:00
904907,51601,16Nov2013:00:00:00
904918,51602,16Nov2013:00:00:00
904918,51603,16Nov2013:00:00:00
904915,51604,16Jan2014:00:00:00
904903,51605,16Jan2014:00:00:00
904917,51606,21Apr2014:00:00:00
904907,51607,21Apr2014:00:00:00
904900,51608,27Dec2015:00:00:00
;
run;






proc sql;
create table work.PAT_ORD_EXISTS as
select
l.PAT_ID
,l.CONTACT_DATE1
,l.CONTACT_DATE2
,(case when exists
(SELECT
PAT_ID
,ORDERING_DATE
FROM work.ORDER_DATES b
WHERE l.PAT_ID=b.PAT_ID
AND (b.ORDERING_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2)
having count(*) >=1)
then 1 else 0 end) as ORDER_EXISTS
from work.CONTACT_DATES l
;
quit;


proc sql;
create table  work.PAT_ORD_EXISTS_ORDERID as
select
l.PAT_ID
,l.CONTACT_DATE1
,l.CONTACT_DATE2
,l.ORDER_EXISTS
,count(b.ORDER_ID) as CNT_ORDER_ID
from work.PAT_ORD_EXISTS l
left join work.ORDER_DATES b on l.PAT_ID=b.PAT_ID
and b.ORDER_ID in (
SELECT
ORDER_ID
from work.ORDER_DATES c
where l.PAT_ID=c.PAT_ID
AND (c.ORDERING_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2)
)
GROUP BY 
l.PAT_ID
,l.CONTACT_DATE1
,l.CONTACT_DATE2
,l.ORDER_EXISTS
;
quit;

proc sql;
select
sum(CNT_ORDER_ID)
from work.PAT_ORD_EXISTS_ORDERID;
quit; /* 62 */


/* Create table that contains the ORDER records where ORDERING_DATE is between
CONTACT_DATE1 and CONTACT_DATE2:   */
proc sql;
create table work.PAT_ORD_EXISTS_ORDERS as
select
l.PAT_ID
,l.CONTACT_DATE1
,l.CONTACT_DATE2
,b.ORDER_ID
,b.ORDERING_DATE
from work.PAT_ORD_EXISTS l
inner join work.ORDER_DATES b on l.PAT_ID=b.PAT_ID
and b.ORDER_ID in (
SELECT
ORDER_ID
from work.ORDER_DATES c
where l.PAT_ID=c.PAT_ID
AND (c.ORDERING_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2)
) ORDER BY l.PAT_ID, l.CONTACT_DATE1
;
quit;


/* Find dispense records of same criteria for patients where ORDER_EXISTS = 1;
same criteria being where DISPENSE_DATE is between CONTACT_DATE1 and CONTACT_DATE2.
Note that dispense records may be missing, or the medication may have never been dispensed.  */





data DISPENSE_DATES;
infile datalines delimiter=',';
input PAT_ID $ DISP_REF_ID 5. DISPENSE_DATE ANYDTDTM19. ;
format DISPENSE_DATE DATETIME19.;
datalines;
904903,89010,31Mar2020:00:00:00
904909,89011,31Mar2020:00:00:00
904911,89012,31Mar2020:00:00:00
904913,89013,31Mar2020:00:00:00
904915,89014,31Mar2020:00:00:00
904918,89015,31Mar2020:00:00:00
904910,89016,31Mar2020:00:00:00
904918,89017,31Mar2020:00:00:00
904914,89018,31Mar2020:00:00:00
904902,89019,31Mar2020:00:00:00
904913,89020,31Mar2020:00:00:00
904916,89021,29Nov2018:00:00:00
904912,89022,29Nov2018:00:00:00
904914,89023,07Jun2019:00:00:00
904906,89024,07Jun2019:00:00:00
904920,89025,07Jun2019:00:00:00
904902,89026,02Apr2020:00:00:00
904902,89027,02Apr2020:00:00:00
904918,89028,02Apr2020:00:00:00
904916,89029,02Apr2020:00:00:00
904915,89030,02Apr2020:00:00:00
904916,89031,02Apr2020:00:00:00
904916,89032,25Jun2020:00:00:00
904912,89033,07Jul2021:00:00:00
904917,89034,07Jul2021:00:00:00
904919,89035,07Jul2021:00:00:00
904920,89036,29Jun2018:00:00:00
904917,89037,29Jun2018:00:00:00
904906,89038,29Jun2018:00:00:00
904913,89039,29Jun2018:00:00:00
904910,89040,06Jul2018:00:00:00
904916,89041,06Jul2018:00:00:00
904907,89042,06Jul2018:00:00:00
904908,89043,06Jul2018:00:00:00
904915,89044,06Jul2018:00:00:00
904919,89045,06Jul2018:00:00:00
904907,89046,02Jul2019:00:00:00
904917,89047,02Jan2020:00:00:00
904906,89048,03Apr2020:00:00:00
904903,89049,03Apr2020:00:00:00
904917,89050,03Apr2020:00:00:00
904909,89051,20Oct2021:00:00:00
904907,89052,20Oct2021:00:00:00
904906,89053,12Sep2016:00:00:00
904910,89054,12Sep2016:00:00:00
904920,89055,12Sep2016:00:00:00
904919,89056,19Sep2016:00:00:00
904919,89057,19Sep2016:00:00:00
904919,89058,19Sep2016:00:00:00
904920,89059,19Sep2016:00:00:00
904913,89060,19Sep2016:00:00:00
904911,89061,19Sep2016:00:00:00
904911,89062,18Dec2016:00:00:00
904913,89063,18Mar2018:00:00:00
904913,89064,18Jun2018:00:00:00
904903,89065,18Jun2018:00:00:00
904914,89066,18Jun2018:00:00:00
904908,89067,04Jan2020:00:00:00
904911,89068,04Jan2020:00:00:00
904911,89069,03Apr2020:00:00:00
904906,89070,03Apr2020:00:00:00
904920,89071,03Apr2020:00:00:00
904916,89072,03Apr2020:00:00:00
904913,89073,03Apr2020:00:00:00
904902,89074,09Apr2021:00:00:00
904918,89075,09Apr2021:00:00:00
904913,89076,09Apr2021:00:00:00
904908,89077,09Apr2021:00:00:00
904906,89078,16Apr2021:00:00:00
904914,89079,16Apr2021:00:00:00
904907,89080,16Apr2021:00:00:00
904909,89081,16Apr2021:00:00:00
904908,89082,16Apr2021:00:00:00
904902,89083,16Apr2021:00:00:00
904919,89084,15Jul2021:00:00:00
904911,89085,12Mar2019:00:00:00
904907,89086,12Apr2020:00:00:00
904908,89087,08Oct2018:00:00:00
904918,89088,16Jan2020:00:00:00
904910,89089,16Jan2020:00:00:00
904920,89090,10Aug2016:00:00:00
904920,89091,10Aug2016:00:00:00
904914,89092,10Aug2016:00:00:00
904903,89093,10Aug2016:00:00:00
904910,89094,17Aug2016:00:00:00
904915,89095,17Aug2016:00:00:00
904903,89096,17Aug2016:00:00:00
904903,89097,17Aug2016:00:00:00
904907,89098,15Nov2016:00:00:00
904903,89099,13Aug2017:00:00:00
904912,89100,16May2018:00:00:00
904915,89101,16May2018:00:00:00
904919,89102,01Mar2020:00:00:00
904916,89103,01Mar2020:00:00:00
904910,89104,01Mar2020:00:00:00
904916,89105,01Mar2020:00:00:00
904908,89106,01Mar2020:00:00:00
904913,89107,01Mar2020:00:00:00
904918,89108,07Aug2016:00:00:00
904912,89109,07Aug2016:00:00:00
904914,89110,07Aug2016:00:00:00
904902,89111,07Aug2016:00:00:00
904907,89112,14Aug2016:00:00:00
904919,89113,14Aug2016:00:00:00
904916,89114,14Aug2016:00:00:00
904913,89115,14Aug2016:00:00:00
904912,89116,14Aug2016:00:00:00
904915,89117,12Nov2016:00:00:00
904900,89118,10Aug2017:00:00:00
904902,89119,13May2018:00:00:00
904911,89120,13May2018:00:00:00
904915,89121,13May2018:00:00:00
904918,89122,27Feb2020:00:00:00
904920,89123,27Feb2020:00:00:00
904900,89124,27Feb2020:00:00:00
904909,89125,27Feb2020:00:00:00
904914,89126,27Feb2020:00:00:00
904914,89127,18May2020:00:00:00
904907,89128,26Mar2021:00:00:00
904920,89129,27Sep2017:00:00:00
904920,89130,27Sep2017:00:00:00
904919,89131,27Sep2017:00:00:00
904914,89132,05Apr2018:00:00:00
904908,89133,05Apr2018:00:00:00
904908,89134,05Apr2018:00:00:00
904918,89135,30Jan2019:00:00:00
904920,89136,30Jan2019:00:00:00
904907,89137,30Jan2019:00:00:00
904900,89138,30Jan2019:00:00:00
904918,89139,24Apr2019:00:00:00
904919,89140,23May2019:00:00:00
904900,89141,25Jun2020:00:00:00
904908,89142,25Jun2020:00:00:00
904911,89143,25Jun2020:00:00:00
904914,89144,25Jun2020:00:00:00
904910,89145,25Jun2020:00:00:00
904910,89146,25Jun2020:00:00:00
904909,89147,25Jun2020:00:00:00
904918,89148,25Jun2020:00:00:00
904920,89149,25Jun2020:00:00:00
904910,89150,25Jun2020:00:00:00
904916,89151,25Jun2020:00:00:00
904913,89152,25Jun2020:00:00:00
904917,89153,25Jun2020:00:00:00
904906,89154,25Jun2020:00:00:00
904911,89155,25Jun2020:00:00:00
904919,89156,25Jun2020:00:00:00
904917,89157,25Jun2020:00:00:00
904916,89158,25Jun2020:00:00:00
904919,89159,25Jun2020:00:00:00
904909,89160,25Jun2020:00:00:00
904906,89161,25Jun2020:00:00:00
904919,89162,25Jun2020:00:00:00
904918,89163,25Jun2020:00:00:00
904912,89164,25Jun2020:00:00:00
904910,89165,25Jun2020:00:00:00
904912,89166,25Jun2020:00:00:00
904919,89167,25Jun2020:00:00:00
904912,89168,25Jun2020:00:00:00
904915,89169,25Jun2020:00:00:00
904910,89170,25Jun2020:00:00:00
904912,89171,25Jun2020:00:00:00
904915,89172,25Jun2020:00:00:00
904918,89173,25Jun2020:00:00:00
904900,89174,25Jun2020:00:00:00
904907,89175,25Jun2020:00:00:00
904909,89176,25Jun2020:00:00:00
904914,89177,25Jun2020:00:00:00
904902,89178,25Jun2020:00:00:00
904906,89179,25Jun2020:00:00:00
904914,89180,25Jun2020:00:00:00
904909,89181,25Jun2020:00:00:00
904917,89182,25Jun2020:00:00:00
904916,89183,25Jun2020:00:00:00
904910,89184,25Jun2020:00:00:00
904900,89185,25Jun2020:00:00:00
904907,89186,25Jun2020:00:00:00
904914,89187,25Jun2020:00:00:00
;
run;


proc sql;
create table  work.PAT_ORD_EXISTS_DISPENSEID as
select
l.PAT_ID
,l.CONTACT_DATE1
,l.CONTACT_DATE2
,l.ORDER_EXISTS
,l.CNT_ORDER_ID
,count(b.DISP_REF_ID) as CNT_DISP_REF_ID
from work.PAT_ORD_EXISTS_ORDERID l
left join work.DISPENSE_DATES b on l.PAT_ID=b.PAT_ID
and b.DISP_REF_ID in (
SELECT
DISP_REF_ID
from work.DISPENSE_DATES c
where l.PAT_ID=c.PAT_ID
AND (c.DISPENSE_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2)
)
GROUP BY 
l.PAT_ID
,l.CONTACT_DATE1
,l.CONTACT_DATE2
,l.ORDER_EXISTS
,l.CNT_ORDER_ID
;
quit;

proc sql;
select
sum(CNT_DISP_REF_ID)
from work.PAT_ORD_EXISTS_DISPENSEID;
quit;   /*  14 */

proc sql;
create table  work.PAT_ORD_EXISTS_DISPENSES as
select
l.PAT_ID
,l.CONTACT_DATE1
,l.CONTACT_DATE2
,l.ORDER_EXISTS
,b.DISP_REF_ID
,b.DISPENSE_DATE
from work.PAT_ORD_EXISTS l
inner join work.DISPENSE_DATES b on l.PAT_ID=b.PAT_ID
and b.DISP_REF_ID in (
SELECT
DISP_REF_ID
from work.DISPENSE_DATES c
where l.PAT_ID=c.PAT_ID
AND (c.DISPENSE_DATE between l.CONTACT_DATE1 AND l.CONTACT_DATE2)
)
ORDER BY l.PAT_ID, l.CONTACT_DATE1
;
quit;

[LinusH]

Your code seems to do more stuff than you are describing in your text, so it's hard to tell if you could've done it smarter.

Given your text only, you could do it in one query with using your BETWEEN..AND as join criteria.