Ok I tried both codes and I still have problems. I did make progress though, and now just need to select from the multiple records. I will try my best to explain:   I need to be able to differentiate a_misuse  from b_misuse. then in the final consolidated dataset (that I need help getting), I'll create a general misuse variable, where if both drugs are misused then misuse=1, if A is misused only, then=2, if B misused only then misuse=3 and if neither misused then misuse=0.    But let me backtrack one moment: I now defined the end date as suggested, giving the widest window:        end_dt= fill_dt + (1+RFL_NBR)*DAYS_SUP;   then, I created a general "end" variable that differs by drug, where: for drugA=1   end=end_dt+7    *remains in system 7 days max; for drugB=1   end=end_dt+30;  *remains in the system longer;   In the data,  there are 3 possibilities for each drug, and one was being missed in previous code:   ----(fill_date)-------[fst_dt]------(end)---  = no misuse, as fst_dt is in the "compliance range"   ----(fill_date)------(end)------[fst_dt]---- = misuse   but there is also:   [fst_dt]-------(fill_dt)----(end) = uncertain - drug test was done after Rx window. They may have had it before data is able to capture?  so I can't  assume misuse or not. so misuse (a_mis or b_mis will = . in these cases).   So using some of both your codes, what I did was get per record, whether a_mis=0, 1, or . and whether b_mis= 0, 1, .  Is there now a way I can select (maybe a proc sql select (min) ?) the lowest value for a_mis, and lowest for b_mis, BY patient and fst_dt?  Meaning if someone has records for a_miss=0, 1, and . for a given fst_dt, I want to select the 0 first. if no 0 then select 1. and if they're all missing then keep as missing. This has to be done for each, a_mis and b_mis.    Here is the current data structure:   data enrolled; input patid fst_dt :YYMMDD10. fill_dt :YYMMDD10. drugA drugB end :YYMMDD10. a_mis b_mis; format fst_dt YYMMDD10. fill_dt YYMMDD10. end YYMMDD10.; cards; 1234 2018-04-11 2018-09-17 1 . 2018-10-12 . . 1234 2018-11-13 2018-10-01 1 . 2018-10-26 1 . 1234 2018-11-13 2019-10-12 1 . 2019-11-21 . . 1234 2018-11-13 2019-11-12 1 . 2019-12-22 . . 1234 2018-11-13 2019-12-12 1 . 2020-01-21 . . 1234 2018-11-13 2018-07-05 . 1 2019-01-31 . 0 1234 2018-11-13 2018-08-10 . 1 2019-04-07 . 0 1234 2018-11-13 2018-11-07 . 1 2019-02-05 . 0 5678 2018-07-17 2017-10-21 1 . 2017-11-30 1 . 5678 2018-10-01 2017-12-19 1 . 2018-01-28 1 . 5678 2019-02-15 2018-01-19 1 . 2018-02-28 1 . 5678 2019-09-11 2018-09-14 1 . 2018-10-24 1 . 5678 2019-09-11 2019-05-31 1 . 2019-07-10 1 . 5678 2019-09-11 2019-07-01 1 . 2019-09-09 1 . 5678 2019-09-11 2019-07-31 1 . 2019-11-08 0 . 5678 2019-09-11 2019-08-30 1 . 2019-10-09 0 . 5678 2019-09-11 2019-11-10 1 . 2019-12-20 . . 5678 2019-09-11 2019-12-10 1 . 2020-01-19 . . 5678 2019-09-11 2019-12-31 1 . 2020-01-13 . . 5678 2019-09-11 2018-07-06 . 1 2018-09-04 . 1 5678 2019-09-11 2018-08-04 . 1 2018-11-02 . 1 5678 2019-09-11 2018-11-01 . 1 2018-12-06 . 1 ; run;         Thanks so much, I hope this is the last step! ... View more

yes that is right - I want to assume misuse unless they've got an Rx in the correct date range to include the drug test date (fst-dt). I am testing this code now still, my SAS is so slow ... View more

yes rfl_nbr is refill number! I have put it to the side for now, but thought if I was to consider the refills, then I could add it to my end-date calculation is that right? so I could do end_date= fill_date+(days_sup*rfl_br)-1   but it get's tricky because maybe a person would not get the refills back to back, there could be variable time in between someone filling an Rx. However with these drugs, it's assumed they're needed daily, round the clock.   The only issue is that one drug has end-date +7 but the other has end_date +30 - is there a way to code it that way after combining drug a and b into one drug var?   Thanks for the help ... View more

I saw this post too late - excuse my convoluted code - it would be overstated to even call me a beginner. I'm just trying to get through my dissertation and be done. Thanks for the code help ... View more

Thanks, it helped to remove a lot of the proc sql's I had in this code and others. I also removed temp files often ... View more

Thanks, I will try this as well - my problem is it doesn't get through the whole macro to allow me to delete anything in between. I suppose I can shorten it too ... View more

Hi Koen,   for the code "C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -WORK "";   You mean I should do exactly like that? I would've thought to just put a disk location that would end in the slash "\" but why the sas.exe?   I just want to make sure. Thank you ... View more

options mcompilenote=all; %macro num (yr, year); *FOR ALL DRUG MATRICES; Data loinc; set udt.loinc_lab&yr; proc sort out=loinc2 nodupkey; by patid fst_dt loinc_cd rslt_txt tst_desc rslt_nbr; run; *schedule 1; proc sql; create table sched as select * from loinc2 where loinc_cd in (select schedule1_a from udt.relevant_all_matrix); quit; run; data sched2; set sched; benzo=0; opioid=0; schedule1=1; *schedule 1 drug; run; *BENZO; proc sql; create table benzo as select * from loinc2 where loinc_cd in (select benzo_a from udt.relevant_all_matrix); quit; run; data benzo2; set benzo; benzo=1; opioid=0; schedule1=0; *benzo; run; *opioid; proc sql; create table opioid as select * from loinc2 where loinc_cd in (select opioid_a from udt.relevant_all_matrix); quit; run; data opioid2; set opioid; benzo=0; opioid=1; schedule1=0; *opioid ; run; *combine the datasets; data combine; set sched2 opioid2 benzo2; if schedule1=. and benzo=. and opioid=. then delete; cov=1; proc sort nodup; by patid fst_dt loinc_cd; run; proc freq data=combine order=freq ; tables schedule1*opioid*benzo /list missing; title1 "&year"; title2 'Combined data for all matrices'; run; *FOR UDT SPECIFIC ONLY; *schedule 1; proc sql; create table sched as select * from loinc2 where loinc_cd in (select schedule1 from udt.relevant_all_matrix); quit; run; data sched2; set sched; benzo=0; opioid=0; schedule1=1; *schedule 1 drug; run; *BENZO; proc sql; create table benzo as select * from loinc2 where loinc_cd in (select benzo from udt.relevant_all_matrix); quit; run; data benzo2; set benzo; benzo=1; opioid=0; schedule1=0; *benzo; run; *opioid; proc sql; create table opioid as select * from loinc2 where loinc_cd in (select opioid from udt.relevant_all_matrix); quit; run; data opioid2; set opioid; benzo=0; opioid=1; schedule1=0; *opioid ; run; data combine2; set sched2 opioid2 benzo2; if schedule1=. and benzo=. and opioid=. then delete; cov=1; proc sort nodup; by patid fst_dt loinc_cd rslt_txt rslt_nbr tst_desc; run; proc freq data=combine2 order=freq ; tables schedule1*opioid*benzo /list missing; title1 "&year"; title2 'Combined data - UDT only sched1*opioid*benzo'; run; Data udt.results&yr; set combine2; * if cov~=1 then delete; *remove those that were not in dataset before; cov2=1; *for calculating age; * yrdob2=year(fst_dt); * age=yrdob2-yrdob; * if age >=18 then ageind=1; *indicator for age to 18+; if schedule1=1 then drug=0; else if benzo=1 then drug=1; else if opioid=1 then drug=2; *SCHEDULE 1; if drug=0 then do; *if rslt_txt in ('Not Applicable', 'Performed', 'Result being held', 'see note', 'SEE NOTE', 'PPS', 'Comment:', 'PRC', 'PINC', 'SPRCS', 'SPQN', 'C', 'RESULT CHECKED', 'SEE BELOW', '-', 'See Note', 'See Comment', 'NA') then do; * if rslt_nbr=0 then s_pos=.; *missing or "unknown"; *end; if rslt_txt in ('positive', 'Positive', 'POSITIVE', '>300', 'DETECTED, SEE BELO', 'POS', 'PRESUMPTIVE POSITI', '>1500', '> 5000', 'PRESENT', '>500', '>600', 'P', '> 2188.18', '>10', '>100', 'MX++PO', '>90', '++POSITIVE++', 'DETECTED', 'Present', '<^10', '>150', '>870', 'Positive^POS', '>5000', '>1000', '>200') then s_pos=1; *positive test for schedule1 drug; else if rslt_txt in ('negative', '<10', 'Negative', 'NEGATIVE', 'NEG', 'NOT DETECTED', 'NONE DETECTED', '<20', '<100', '<5', 'N', 'Neg', 'NORMAL', 'Not Detected', 'NEG.', 'None Detected', 'NEGATIVE CONFIRMED', 'NOTDET', 'neg', 'NULL', '<200', 'Negative^NEG', 'NOT DET', 'Negative^Negative', '<50', '<^20', '<15', '<^15', 'None detected') then s_pos=0; if s_pos=. then do; if rslt_nbr=0 then numbs=0; if rslt_nbr>0 then numbs=1; end; *edit this with specific loinc cutoffs (excel sheet- "cutoffs" from CPT_years file and consider removing those with test saying TNP or not reported, etc.; if numbs=1 then do; s_pos=1; end; end; *BENZOs; if drug=1 then do; *if rslt_txt in ('Comment:', 'DNR', 'Not Applicable', 'PINC', 'PRC', 'Performed', 'Result being held', 'SEE NOTE', 'SPRCS', 'TNP', 'TNP/130', 'Test not performed', 'see note', 'PPS', 'SEE BELOW', 'See Final Results', 'QNS', 'RESULT CHECKED', 'TNP/130', '-', '**Cancelled**', 'TNP/317', 'SEE COMMENT', 'TNP/120', 'Test not performed', 'SEE COMMENTS', 'See Note', 'TNP/109', 'TNP/154', 'TNPR124') then do; *if rslt_nbr=0 then b_pos=.; *missing or "unknown"; * if rslt_nbr>1 then b_pos=4; *possibly positive--see how many first; *end; if rslt_txt in ( '> 50000', '>10000', '>2000', '>3000', '>500', 'DETECTED BY IMMUNO', 'DETECTED, SEE BELO', 'Non-negative', 'POS', 'POSITIVE', 'PRESUMPTIVE POSITI', 'Positive', 'Presumptive positi', 'positive', 'P', 'MX++PO', '>1000', '>5000', 'DETECTED', 'Present', 'PRESENT', 'Detected', 'Non-negative', '>6250', '>400', 'DETECTED, SEE BELO', '>3000', '>200', '> 10000', '>40000', 'Positive^POS', '+', 'Pos', '> 100000', '> 38284.83', '> 53995.68', '>2500') then b_pos=1; *positive test for benzo; else if rslt_txt in ( '<100', '<50', 'N', 'NEG', 'NEG.', 'NEGATIVE', 'NEGATIVE CONFIRMED', 'NONE DETECTED', 'NOT DETECTED', 'Neg', 'Negative', 'None Detected', 'Not Detected', 'negative', 'NOTDET', 'NEGATIVE', '<20', '<100', 'neg', '<5', '<^20', 'NULL', 'NOND', 'Negative^NEG', 'NODRUG', '<25', '<40', 'Negative^Negative', 'NOT DET') then b_pos=0; if b_pos=. then do; if rslt_nbr=0 then numbb=0; if rslt_nbr>0 then numbb=1; end; *edit this with specific loinc cutoffs (excel sheet- "cutoffs" from CPT_years file and consider removing those with test saying TNP or not reported, etc.; if numbb=1 then do; b_pos=1; end; end; *OPIOIDS; if drug=2 then do; *if rslt_txt in ( '*', 'Comment:', 'DNR', 'Not Applicable', 'PINC', 'PRC', 'Performed', 'Result being held', 'SEE NOTE', 'SEE NOTE#7', 'SPRCS', 'PPS', 'See Final Results', 'SEE BELOW', 'SPQN', 'SEE COMMENT', 'RESULT CHECKED', '-', 'SEE COMMENTS', '**Cancelled**', 'Comment', 'C', 'Complete', 'NA') then do; *if rslt_nbr=0 then o_pos=.; *missing or "unknown"; * if rslt_nbr>1 then o_pos=4; *possibly positive--see how many first; *end; if rslt_txt in ( '++POSITIVE++', '> 100000', '> 2000', '> 5000', '> 50000', '> 58892.81', '> 645161.29', '> 68965.51', '> 75872.53', '> 79491.25', '>100', '>1000', '>10000', '>100000', '>15000', '>2000', '>20000', '>3000', '>500', '>5000', '>60000', '>75000', '>90000', 'DETECTED BY IMMUNO', 'DETECTED, SEE BELO', 'Non-negative', 'P', 'POS', 'POSITIVE', 'PRESENT', 'PRESUMPTIVE POSITI', 'MX++PO', 'MXPRES', 'DETECTED', '>1500', '>50000', '> 12500', '>13889', '>4000', '+', '>20', '>14925', '>5780', 'DETECTED BY GC/MS', 'DETECTED, CONFIRME', 'Positive^POS', '> 43994.72', '> 36873.15', '>2500', '> 10000', '> 111607.14', '> 140449.43', '> 32679.73', '> 46125.46', '> 47824', '> 48851.97', '> 68870.52', '> 72568.94', '> 72727.27', '> 90415.91', '> 99601.59', '>200', '>400', '>455', '>500.0', '>6000') then o_pos=1; *positive test for opioid; else if rslt_txt in ( '< 0.50', '<1', '<100', '<20', '<200', '<5', 'N', 'NEG', 'NEG.', 'NEGATIVE', 'NEGATIVE CONFIRMED', 'NONE DETECTED', 'NOT DETECTED', 'NOTDET', '<^20', 'NULL', '<50', 'Negative^Negative', 'Negative^NEG', 'NOT DET', 'NODRUG', '<8', '<1.0', 'NOND', '<2', '<^12', '<^8', '<25', '< 5', 'n') then o_pos=0; if o_pos=. then do; if rslt_nbr=0 then numbo=0; if rslt_nbr>0 then numbo=1; end; *edit this with specific loinc cutoffs (excel sheet- "cutoffs" from CPT_years file and consider removing those with test saying TNP or not reported, etc.; if numbo=1 then do; o_pos=1; end; end; if mdy(01,01,2013)<= fst_dt <= mdy(03,31,2013) then quarter=1; if mdy(04,01,2013)<= fst_dt <= mdy(06,30,2013) then quarter=2; if mdy(07,01,2013)<= fst_dt <= mdy(09,30,2013) then quarter=3; if mdy(10,01,2013)<= fst_dt <= mdy(12,31,2013) then quarter=4; if mdy(01,01,2014)<= fst_dt <= mdy(03,31,2014) then quarter=5; if mdy(04,01,2014)<= fst_dt <= mdy(06,30,2014) then quarter=6; if mdy(07,01,2014)<= fst_dt <= mdy(09,30,2014) then quarter=7; if mdy(10,01,2014)<= fst_dt <= mdy(12,31,2014) then quarter=8; if mdy(01,01,2015)<= fst_dt <= mdy(03,31,2015) then quarter=9; if mdy(04,01,2015)<= fst_dt <= mdy(06,30,2015) then quarter=10; if mdy(07,01,2015)<= fst_dt <= mdy(09,30,2015) then quarter=11; if mdy(10,01,2015)<= fst_dt <= mdy(12,31,2015) then quarter=12; if mdy(01,01,2016)<= fst_dt <= mdy(03,31,2016) then quarter=13; if mdy(04,01,2016)<= fst_dt <= mdy(06,30,2016) then quarter=14; if mdy(07,01,2016)<= fst_dt <= mdy(09,30,2016) then quarter=15; if mdy(10,01,2016)<= fst_dt <= mdy(12,31,2016) then quarter=16; if mdy(01,01,2017)<= fst_dt <= mdy(03,31,2017) then quarter=17; if mdy(04,01,2017)<= fst_dt <= mdy(06,30,2017) then quarter=18; if mdy(07,01,2017)<= fst_dt <= mdy(09,30,2017) then quarter=19; if mdy(10,01,2017)<= fst_dt <= mdy(12,31,2017) then quarter=20; if mdy(01,01,2018)<= fst_dt <= mdy(03,31,2018) then quarter=21; if mdy(04,01,2018)<= fst_dt <= mdy(06,30,2018) then quarter=22; if mdy(07,01,2018)<= fst_dt <= mdy(09,30,2018) then quarter=23; if mdy(10,01,2018)<= fst_dt <= mdy(12,31,2018) then quarter=24; if mdy(01,01,2019)<= fst_dt <= mdy(03,31,2019) then quarter=25; if mdy(04,01,2019)<= fst_dt <= mdy(06,30,2019) then quarter=26; if mdy(07,01,2019)<= fst_dt <= mdy(09,30,2019) then quarter=27; if mdy(10,01,2019)<= fst_dt <= mdy(12,31,2019) then quarter=28; if quarter in (1,2,3,4) then year=2013; if quarter in (5,6,7,8) then year=2014; if quarter in (9,10,11,12) then year=2015; if quarter in (13,14,15,16) then year=2016; if quarter in (17,18,19,20) then year=2017; if quarter in (21,22,23,24) then year=2018; if quarter in (25,26,27,28) then year=2019; run; *proc freq data=udt.results&year order=freq; *tables o_pos b_pos s_pos numbs numbo numbb/list missing; * title1 "&year" ; * title2 "missing results"; *run; *proc sort nodupkey data=results2 out=total; *by patid fst_dt; *where drug~=. and panelind~=1; *panelind=1 means excluding those=0 (naloxone, nicotine, barbiturates etc); *proc freq data=schedule1; *table year; *title1 "&year"; *title2 'total drug tests/year - all test matricies, all ages'; *run; proc sort nodupkey data=udt.results&yr out=schedule1; by patid fst_dt; where (drug=0); proc freq data=schedule1; table quarter; title1 "&year"; title2 ' # of schedule1 test each quarter'; run; proc sort data=udt.results&yr out=schedule1_&yr; by patid fst_dt; where (s_pos in (0 1)); run; proc sort nodupkey; by patid fst_dt; proc freq; table quarter/out=out1; title1 "&year"; title2 ' # of schedule 1 test with interpretable results each quarter'; run; proc sort data=udt.results&yr out=schedule1; by patid fst_dt; where (s_pos in (1)); proc sort nodupkey data=schedule1; by patid fst_dt; run; proc freq data=schedule1; table quarter/out=out2; title1 "&year"; title2 ' # of schedule 1 tests=POSITIVE, each quarter'; run; data ss&yr; merge out1(keep=quarter count rename=(count=denom)) out2(keep=quarter count rename=(count=num)); by quarter; rate_=(num/denom)*100; proc print; var quarter rate_; title1 "&year"; title2 ' % positive schedule1 per quarter'; run; proc sort nodupkey data=udt.results&yr out=benzo; by patid fst_dt; where (drug=1); proc sort nodupkey data=udt.results&yr out=opioid; by patid fst_dt; where (drug=2); data concurrent; merge benzo(in=a keep=patid fst_dt quarter) opioid(in=b keep=patid fst_dt quarter); by patid fst_dt; if a and b; proc freq; table quarter; title1 "&year"; title2 ' # of opioid and benzo concurrent test each quarter'; run; proc sort data=udt.results&yr out=benzo; by patid fst_dt; where (b_pos in (0 1)); run; proc sort nodupkey data=benzo; by patid fst_dt; proc sort data=udt.results&yr out=opioid; by patid fst_dt; where (o_pos in (0 1)); run; proc sort nodupkey data=opioid; by patid fst_dt; run; data concurrent_&yr; merge benzo(in=a keep=patid fst_dt quarter b_pos) opioid(in=b keep=patid fst_dt quarter o_pos);by patid fst_dt; if a and b; proc freq data=concurrent_&yr; table quarter/out=out1; title1 "&year"; title2 ' # of opioid and benzo concurrent test WITH results each quarter'; run; proc sort data=udt.results&yr out=benzo; by patid fst_dt; where (b_pos in (1)); run; proc sort nodupkey data=benzo; by patid fst_dt; proc sort data=udt.results&yr out=opioid; by patid fst_dt; where (o_pos in (1)); run; proc sort nodupkey data=opioid; by patid fst_dt; run; data concurrent; merge benzo(in=a keep=patid fst_dt quarter) opioid(in=b keep=patid fst_dt quarter); by patid fst_dt; if a and b; proc freq data=concurrent; table quarter/out=out2; title1 "&year"; title2 ' # of opioid and benzo POSITIVE each quarter'; run; data ob&yr; merge out1(keep=quarter count rename=(count=denom)) out2(keep=quarter count rename=(count=num)); by quarter; rate_=(num/denom)*100; yr=&year; proc print; var quarter rate_; title1 "&year"; title2 ' % positive concurrent use rates per quarter '; run; /** 1 1 0.11655 2 2 0.10521 3 3 0.09594 4 4 0.09639 **/ ***************************************************************************************************************************; %mend num; %num (13, 2013); %num (14, 2014); %num (15, 2015); %num (16, 2016); %num (17, 2017); %num (18, 2018); %num (19, 2019); ... View more

Hi Amir, Yes I went back to using the work library since the external disk was also giving me issues. I will add the code now but it is long. ... View more

whoa, that's it?! I was trying to do a do loop with an indicator for each month prior and proc sql -_-   Thanks!! ... View more

data enrolled; input patid fst_dt :YYMMDD10. eligeff :YYMMDD10. eligend :YYMMDD10.; format fst_dt YYMMDD10. eligeff YYMMDD10. eligend YYMMDD10.; cards; 123 2017-12-13 2017-07-01 2019-05-31 123 2019-02-06 2017-07-01 2019-05-31 212 2018-07-17 2017-10-01 2019-12-31 212 2018-10-01 2017-10-01 2019-12-31 212 2019-02-15 2017-10-01 2019-12-31 212 2019-06-10 2017-10-01 2019-12-31 212 2019-09-11 2017-10-01 2019-12-31 540 2019-05-21 2017-12-01 2019-12-31 ; proc print; run; *need to create an indicator variable "eligible" if, a record (each observation line) has continuous enrollment 365 days prior to index. so (fst_date)-365 =all those date should be included between eligeff and eligend obs#3 would Not be eligible, obs#4 would be eligible; ... View more