Thanks so much for taking the time to help me with this requirement. I'm amazed how you are able to figure this stuff out so quickly. I learned a lot by dissecting your code example. Thank you, thank you, thank you! ... View more

Hi. It's because in that case the PI was visited on two consecutive visits in a row. The requirement is Recycle_Ind=1 when the ID visits different PI in between visits. Since the ID 234567 visited PI 3234567890 two consecutive times the second record should not get marked as a recycled. Sorry if I wasn't clear on that. I do really appreciate your time in providing a solution. You have helped me so often in the past. ... View more

Thanks so much for sharing. It definitely appears to work, but I just can't understand it - and therefore don't think I should introduce this code into our environment - since I won't be able to troubleshoot or enhance it later on. Thanks again for trying to help. I really do appreciate your time. I wish I code code like you do. ... View more

Wow. This does appear to work. I'm going to do some testing and I'll follow up. May I ask, why does the code still contain the condition "OLD_THRU < (CLM_FROM_DT-1)"? I'm curious why this condition is still necessary? ... View more

Wow! This code is working even with my new set of test data that broke the prior two solution offerings. Unfortunately the program's assumption that "no PI is attached to more than one ID". This indeed can happen in my data so I guess this breaks the proposed solution. 😞 Not understanding this type of programming (eg. Hash) I can't begin to alter this program. Is there anyway this solution can be coded in more traditional SAS datasteps? ... View more

Hi. Unfortunately this isn't working either when I tested it with this second set of test records.  Here's what I'm expecting to be flagged based on the requirement.  If you run the code below it is flagging differently.  Any suggestions based on this?   123456 1234567890 14SEP2017 12NOV2017 123456 1234567890 15NOV2017 11JAN2018 123456 2345678901 27FEB2018 27APR2018 123456 2345678901 28APR2018 26JUN2018 123456 2345678901 27JUN2018 25AUG2018 234567 3234567890 14SEP2017 12FEB2018 234567 4345678901 20FEB2018 25FEB2018 234567 3234567890 12MAR2018 08MAY2018     Recycle_Ind=1 234567 3234567890 11MAY2018 12MAY2018 234567 4345678901 13MAY2018 09JUL2018     Recycle_Ind=1 234567 3234567890 10JUL2018 07SEP2018     Recycle_Ind=1       data have; input ID PI (FROM_DT THRU_DT) (:date9.); format FROM_DT THRU_DT yymmdd10. pi 10.; datalines; 123456 1234567890 14SEP2017 12NOV2017 123456 1234567890 15NOV2017 11JAN2018 123456 2345678901 27FEB2018 27APR2018 123456 2345678901 28APR2018 26JUN2018 123456 2345678901 27JUN2018 25AUG2018 234567 3234567890 14SEP2017 12FEB2018 234567 4345678901 20FEB2018 25FEB2018 234567 3234567890 12MAR2018 08MAY2018 234567 3234567890 11MAY2018 12MAY2018 234567 4345678901 13MAY2018 09JUL2018 234567 3234567890 10JUL2018 07SEP2018 ; proc sql; select *, exists ( select * from have as b inner join have as c on c.id = b.id and c.pi ne b.pi and c.from_dt < b.from_dt where b.id = a.id and b.pi = a.pi and intck("DAY", a.thru_dt, b.from_dt) between 1 and 180 and c.thru_dt > a.thru_dt ) as recycle from have as a; quit; ... View more

Unfortunately this logic didn't work.  I've included new example data and you can see that it's flagging too many rows with Recycle_Ind=1 .  It has to do with this line of code:  if OldThru < (FROM_DT-1) and (FROM_DT - OldThru) < 180 then Recycle_ID = 1; This assumes that there will only be one day's difference between the current FROM_DT and the previous THRU_DT, when in fact there could be a larger gap. I extended the gap by more than 1 day for the records that are marked as incorrect below.   If you have any thoughts on how to correct this I'd love to get this working.   For the input data below the following records are identified: ID     PI         FROM_DT   THRU_DT 123456 1234567890 14SEP2017 12NOV2017 123456 1234567890 15NOV2017 11JAN2018 INCORRECTLY RECYCLE=1 123456 2345678901 27FEB2018 27APR2018 123456 2345678901 28APR2018 26JUN2018 123456 2345678901 27JUN2018 25AUG2018 234567 3234567890 14SEP2017 12FEB2018 234567 4345678901 20FEB2018 25FEB2018 234567 3234567890 12MAR2018 08MAY2018 RECYCLE=1 234567 3234567890 11MAY2018 12MAY2018 INCORRECTLY RECYCLE=1 234567 4345678901 13MAY2018 09JUL2018 RECYCLE=1 234567 3234567890 10JUL2018 07SEP2018 RECYCLE=1           data have; informat FROM_DT date9. THRU_DT date9.; format FROM_DT date9. THRU_DT date9.; input ID PI FROM_DT THRU_DT; datalines; 123456 1234567890 14SEP2017 12NOV2017 123456 1234567890 15NOV2017 11JAN2018 123456 2345678901 27FEB2018 27APR2018 123456 2345678901 28APR2018 26JUN2018 123456 2345678901 27JUN2018 25AUG2018 234567 3234567890 14SEP2017 12FEB2018 234567 4345678901 20FEB2018 25FEB2018 234567 3234567890 12MAR2018 08MAY2018 234567 3234567890 11MAY2018 12MAY2018 234567 4345678901 13MAY2018 09JUL2018 234567 3234567890 10JUL2018 07SEP2018 ; run;   * sort to group ID's on PI values; proc sort data=have out=want; by ID PI FROM_DT; run;   * pick ID with recurrent PI, not concecutive, but within 180 days; data w3 (drop=OldPI OldThru); set wANT; by ID PI; OldPI = lag(PI); OldThru = lag(THRU_DT); if not first.PI then do; if OldThru < (FROM_DT-1) and (FROM_DT - OldThru) < 180 then Recycle_ID = 1; end; run; ... View more

This looks interesting, but I have no idea what it's doing. This is the first time I've ever seen this 'hash' concept. I'm afraid this is over my head. 😞 ... View more

This also looks like it will work. I'll try this out today too. Thank you. ... View more

I will try this out today and reply back. Thank you. ... View more

Each time an ID switches away from and then back to a previous PI within 180 days of the last visit, I need to mark the current record with Recycle_Ind=1. In your example 2 records would qualify. Does this make better sense? 1 A JAN 1 A FEB 1 B MAR 1 C APR 1 A MAY RECYCLE=1 1 B JUN RECYCLE=1 ... View more

For the statement 'ods select Variables;'. Is the word Variables a SAS keyword that means something specific? ... View more

Thanks so much for the help. This is perfect. I appreciate you taking the time to help. ... View more

wow this gives me great information.  Thank you.  Is there anyway to output this as a SAS dataset? ... View more