I'm stuck on this puzzling requirement

[buechler66]

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?

[Peter_C]

As i understand this requirement, you are seeking ID which return to a PI within 6mths having tried an alternative PI in those 6mths.
Looks like an ID might do this more than once with 2 more PI
1 A JAN
1 A FEB
1 B MAR
1 C APR
1 A MAY
1 B JUN
The ID=1 has returned to both A and to B between Jan and Jun but might return to C in the next 4 months.
What is the structure of the result required?

[buechler66]

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

[DanielLangley]

Sorry I have had to use a hash to get this to work.

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
165074 1699130682 14SEP2017 12NOV2017
165074 1699130682 13NOV2017 11JAN2018
165074 1255743092 27FEB2018 27APR2018
165074 1255743092 28APR2018 26JUN2018
165074 1043257041 27JUN2018 25AUG2018
176368 1720270135 20OCT2017 18DEC2017
176368 1487654356 12MAR2018 10MAY2018
176368 1720270135 11MAY2018 09JUL2018
176368 1987654359 10JUL2018 07SEP2018
;

proc sort data = work.have
			out = work.havesrt;
by id from_dt;
run;


data work.want(drop = Save_DT Save_PI irc rc);
declare hash Prev(ordered:'a');
 prev.definekey('Save_DT');
 prev.definedata('Save_PI');
 prev.definedone();
declare hiter iprev('prev');
format 	save_pi 10.
		save_dt yymmdd10.;			

do until (eof);
	/*** Go through the information for a customer one at a time ***/
	do until (last.id);
		set work.havesrt end = eof;
		by id; * lets us look at only one customer;
		call missing(Recycle_Ind); * make sure this doesn't carry over between rows;
		irc=iprev.last(); *get the most recent information for the customer;
		/*** The most recent information must exist and must have a different PI ***/
		if irc = 0 and Save_PI ne PI then do until(irc or From_DT - Save_DT gt 180);
			if Save_PI eq PI then do; 
				Recycle_Ind = 1;
				leave;
				end;
			irc=iprev.prev();
		end;
		output;
		rc=prev.add(key:Thru_dt, data:PI);
	end;


	/*** I am still quite new to hash and haven't figured out a
	better way of clearing a hash if a hiter is pointing at it other
	than to force the hiter to not look at it and then clear it ***/
	irc=iprev.first();
	irc=iprev.prev();
	/*** Get rid of all the information in the hash to save space and mean that future
	id's aren't looking at a different id's worth of data ***/
	rc=prev.clear();
end;
stop;
run;

[buechler66]

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.

[DanielLangley]

I do really recommend trying to learn hash objects as they are very flexible. This solution might be easier to understand but it does have a limit on the number of rows per ID, currently 1000 as in the array definition. You might need to check if that holds or increase the number if necessary.

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
165074 1699130682 14SEP2017 12NOV2017
165074 1699130682 13NOV2017 11JAN2018
165074 1255743092 27FEB2018 27APR2018
165074 1255743092 28APR2018 26JUN2018
165074 1043257041 27JUN2018 25AUG2018
176368 1720270135 20OCT2017 18DEC2017
176368 1487654356 12MAR2018 10MAY2018
176368 1720270135 11MAY2018 09JUL2018
176368 1987654359 10JUL2018 07SEP2018
;

proc sort data = work.have
			out = work.havesrt;
by id from_dt;
run;

data work.want(drop=i savedpicount);
retain SavedPiCount;
set work.havesrt;
by id;

array comparison[2,1000] _temporary_;
call missing(Recycle_Ind);

/* First ID cannot be recycle so don't bother checking it just add it to the array */
if first.id then SavedPiCount = 0;
/* All other ID's must be checked against those already in the array.
First check that the current Pi is different to the previous Pi
then check if any other Pi's in the past 180 days are the same as the current Pi */
else if PI ne comparison[2,SavedPiCount] then do i = SavedPiCount to 1 by -1;
	if comparison[2,i] = PI and from_dt - comparison[1,i] lt 180 then do;
		Recycle_Ind = 1;
		leave;
		end;
end;

/* Once the logic has been checked for this Pi then add it to the array */
SavedPiCount = sum(SavedPiCount,1);
comparison[1,SavedPiCount] = Thru_dt;
comparison[2,SavedPiCount] = PI;
run;

[buechler66]

Thanks so much for this. I love to see how you pro coders tackle these
types of problems. Thanks again for your take on this. Much appreciated!