topic Re: I'm stuck on this puzzling requirement in SAS Programming

I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 11:28:39 GMT

This is a tough one. I don't even know if this is possible. My requirement is to identify any instance where an ID switches from away from his PI, but later switches back to that same PI within 180 days. There can be any number of PI switches in between the original visit and the return visit.

So in the sample data below ID 165074 switches from PI 1699130682 to 1255743092 to PI 1043257041. But he never switches back to any of the earlier visited PIs.

However, ID 176368 switches from PI 1720270135 on THRU_DT 18DEC2017 to PI 1487654356, and later switches back to PI 1720270135 on FROM_DT 11MAY2018. And since the difference between these two dates is within 180 days I'd want to set an indicator of Recycle_ID=1 for this ID. The difference in days is calculated as (FROM_DT - THRU_DT of the repeat visits).

Does anyone have any ideas on how to accomplish this? I can't figure out a way to do this. Any help would be really appreciated.

In the example data below I've noted the record that would get the Recycle_Ind set to 1.

* Data sorted by ID and FROM_DT

ID PI FROM_DT THRU_DT
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 <= RECYCLE_IND=1 (11MAY18-18DEC17) IS <= 180
176368 1987654359 10JUL2018 07SEP2018

Re: I'm stuck on this puzzling requirement

ErikLund_Jensen — Tue, 05 Mar 2019 19:40:10 GMT

Hi @buechler66

This is a problem where then LAG function is useful. It just marks one observation, and you will probably need some further processing to get your output.

data have;
	informat FROM_DT date9. THRU_DT date9.;
	format FROM_DT date9. THRU_DT date9.;
	input ID PI FROM_DT THRU_DT;
	datalines;
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
;
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 w1; 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;

Re: I'm stuck on this puzzling requirement

PGStats — Tue, 05 Mar 2019 23:10:21 GMT

Not obvious indeed. But here is a shot:

data have;
input ID PI (FROM_DT THRU_DT) (:date9.);
format FROM_DT THRU_DT yymmdd10. pi 10.;
datalines;
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 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;

                     ID          PI     FROM_DT     THRU_DT   recycle
               ------------------------------------------------------
                 165074  1699130682  2017-09-14  2017-11-12         0
                 165074  1699130682  2017-11-13  2018-01-11         0
                 165074  1255743092  2018-02-27  2018-04-27         0
                 165074  1255743092  2018-04-28  2018-06-26         0
                 165074  1043257041  2018-06-27  2018-08-25         0
                 176368  1720270135  2017-10-20  2017-12-18         1
                 176368  1487654356  2018-03-12  2018-05-10         0
                 176368  1720270135  2018-05-11  2018-07-09         0
                 176368  1987654359  2018-07-10  2018-09-07         0

Would require testing with a complete range of possible cases.

Re: I'm stuck on this puzzling requirement

mkeintz — Wed, 06 Mar 2019 00:34:27 GMT

If the data is already sorted by ID/FROM_DT then you only need to save the most recent THRU_DT + 180 for each PI, which can be tested against the incoming FROM_DT for the same PI

data have;
	informat FROM_DT date9. THRU_DT date9.;
	format FROM_DT date9. THRU_DT date9.;
	input ID PI FROM_DT THRU_DT;
	datalines;
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
run;

data want (drop=_:);
  set have;
  by id pi notsorted;
  if _n_=1 then do;
    _thru_plus_180=.;
    declare hash h ();
      h.definekey('pi');
      h.definedata('_thru_plus_180');
      h.definedone();
  end;
  if h.find()=0 and first.pi=1 and from_dt<=_thru_plus_180 then repeat='Y';
  else repeat=' ';
  if last.pi then h.replace(key:pi,data:thru_dt+180);
run;

The hash object is keyed on the PI values, and the program assumes that no PI is attached to more than one ID. Otherwise you have to modify the above to avoid contaminating tests for one ID with dates from a prior ID, as here:
change the h.definekey method to:
h.definekey('ID','PI');
and change the h.replace method to
h.replace(key:id,key:pi,data:thru_dt+180);
The "SET HAVE; BY ID PI NOTSORTED;" statements tell sas to expect the data in groups, not neccessarily sorted. This is simply a tool to generate the dummy variables first.pi and last.pi. So you can test date comparisons every time a PI group starts, and also update the hash object every time you are at the end of a PI group.

Re: I'm stuck on this puzzling requirement

Peter_C — Wed, 06 Mar 2019 10:19:56 GMT

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?

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 10:35:22 GMT

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

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 11:30:41 GMT

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

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 11:31:52 GMT

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

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 11:34:23 GMT

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. 😞

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 15:46:01 GMT

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;

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 15:58:27 GMT

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;

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 16:09:02 GMT

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?

Re: I'm stuck on this puzzling requirement

ErikLund_Jensen — Wed, 06 Mar 2019 17:07:05 GMT

Hi @buechler66

I am sorry it did'nt work, that was because I assumed that intervals were consecutive, which caused gaps between same values of PI to be marked also. The following code marks a recycle only if there has been (at least) one other PI value in between.

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;

* Just to make sure input is in concecutive order by ID FROM_DT;
proc sort data=have out=w1;
	by ID FROM_DT;
run;

* Add a sequence number; 
data w2; set w1;
	Seq = _N_;
run;

* sort to group ID's on PI values;
proc sort data=w2 out=w3; 
	by ID PI FROM_DT;
run;

* Pick ID with recurrent PI, not consecutive, but within 180 days;
* Use sequence number to suppress gaps between same PID's;
data w4 (drop=OldPI OldThru OldSeq Seq); set w3; by ID PI;
	OldPI = lag(PI);
	OldThru = lag(THRU_DT);
	OldSeq = lag(Seq);

	if not first.PI and Seq ne (OldSeq+1) then do;
			if OldThru < (FROM_DT-1) and (FROM_DT - OldThru) < 180 then Recycle_ID = 1;
	end;
run;

* Sort to restore input order;
proc sort data=w4 out=want;
	by ID FROM_DT;
run;

Re: I'm stuck on this puzzling requirement

DanielLangley — Wed, 06 Mar 2019 17:32:17 GMT

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;

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 17:39:04 GMT

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?

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 17:46:22 GMT

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.

Re: I'm stuck on this puzzling requirement

ErikLund_Jensen — Wed, 06 Mar 2019 18:45:58 GMT

Hi @buechler66

I just forgot to remove it, because it does no harm. It had a purpose in my first attempt, where I thought the intervals were consecutive, so a gap meant that there was an interval with another PI in between. But the check on sequence number makes it obsolete.

Re: I'm stuck on this puzzling requirement

PGStats — Wed, 06 Mar 2019 20:06:11 GMT

Please explain

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 WHY? 11MAY2018 - 12FEB2018 < 180 days
234567 4345678901 13MAY2018 09JUL2018 Recycle_Ind=1
234567 3234567890 10JUL2018 07SEP2018 Recycle_Ind=1

Re: I'm stuck on this puzzling requirement

DanielLangley — Wed, 06 Mar 2019 21:01:21 GMT

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;

Re: I'm stuck on this puzzling requirement

buechler66 — Wed, 06 Mar 2019 21:50:16 GMT

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.