Changing value of previous record based on value in current record

[alaxman]

Hello,

This is my data:

Patient ID	svcdate	daysupp	prodnme	daysupp_new
123	9/29/14	7	ZUBSOLV	-
123	10/2/14	30	ZUBSOLV	-
123	10/9/14	30	NALTREXONE HCL	7

I've computed daysupp_new as 7 for when prodnme = NALTREXONE HCL

Now I want to update the daysupp value in row #2 with the daysupp_new value from row #3 so that the data looks like this:

Patient ID	svcdate	daysupp	prodnme	daysupp_new
123	9/29/14	7	ZUBSOLV	-
123	10/2/14	7	ZUBSOLV	-
123	10/9/14	30	NALTREXONE HCL	7

How can I do this?

[Reeza]

What’s the logic/rule? That just for that single row or does it need to generalize to other data?

[jimbarbour]

@Reeza,

I think the below graphic may illustrate what (I think) @alaxman is after.  Basically, when on row 2, if the daysupp_new variable is populated on the next record (row 3), update daysupp on row 2 with the value in daysupp_new on row three.  I'm recommending the "hop" macro from @LeonidBatkhan which performs a read ahead (the equivalent of a LEAD() function if there were one in SAS).  Link:  https://blogs.sas.com/content/sgf/2017/11/01/hopping-for-the-best-calculations-across-sas-dataset-ob...

Jim

[LeonidBatkhan]

Thank you @jimbarbour!

While the %hop macro should work fine here, an alternative way of calculating DAYSUPP_NEW could be:

1. sort the data set in reverse chronological order;
2. use lag() function, as now your "next" raw becomes your "previous" row;
3. restore/sort the data set in chronological order.

Here is a code example illustrating this alternative method:

proc sort data=HAVE out=REVERSE;
   by PATIENT_ID descending SVCDATE;
run;

data WANT (drop=PREDATE);
   set REVERSE;
   PREDATE = lag(SVCDATE);
   if PRODNME eq 'NALTREXONE HCL' then DAYSUPP_NEW = PREDATE;
run;

proc sort data=WANT;
   by PATIENT_ID SVCDATE;
run;

Hope this helps.

[alaxman]

@jimbarbour 

How would I use the "hop" function within this macro that i've written to get the "daysupp_new" - in order to change "daysupp" -- exactly how you have illustrated in the table below. 

%macro hop(d,x,y,j);

   _p_ = _n_ + &j;

   if (1 le _p_ le _o_) then set &d(keep=&x rename=(&x=&y)) point=_p_ nobs=_o_;

%mend hop;

data pers_3M_12M_30DAY (drop=TEMP1);

set pers_3M_12M_30DAY;

if prodnme in ("VIVITROL", "REVIA", "NALTREXONE HCL", "NALTREXONE")

and svcdate_new le lag(svcdate_new)+ lag(daysupp)

thendo;

    %hop(pers_3M_12M_30DAY,SVCDATE_NEW, TEMP1,-1)

         DAYSUPP_NEW= SVCDATE-TEMP1;

end;

run;

[Reeza]

Do you have SAS ETS licensed? If so, you can also tweak things so PROC EXPAND and LEAD work for what you need.

[alaxman]

@jimbarbour

Sorry I meant to ask this as a question & forgot the question mark. I have used the "hop" function as I note in my code that I recently posted to get the "7" figure as illustrated in your table. But how would I then change "daysupp" to "7" next to the svcdate "10/2" in the table?

Thank you!

[jimbarbour]

@alaxman,

The data I have is as follows below.  If you have more data, I'm happy to run it through, but the program I've written (I'll get to that in a minute) should be able to handle different data.

DATA	Drug_Data;
	INFILE	Datalines	DSD	DLM='09'x	MISSOVER;

	INPUT	Patient_ID	$
			SvcDate		:	ANYDTDTE8.
			DaySupp
			ProdNme		:	$25.
			;
	FORMAT	SvcDate		mmddyys10.;

Datalines;
123	9/29/14	7	ZUBSOLV
123	10/2/14	30	ZUBSOLV
123	10/9/14	30	NALTREXONE HCL
;
RUN;

Next, with apologies to @LeonidBatkhan, I modified the Hop macro slightly to set a Boolean, _Found.  If a record is returned, _Found =1, otherwise, _Found = 0.  I wanted to NOT execute the code to update DaySupp if no next record were found.

%MACRO	Hop(d, x, y, j);
	_p_	=	_n_ + &j;
	IF	(1 <= _p_ <= _o_)	THEN
		DO;
			_Found	=	1;
			SET	&d(KEEP=&x RENAME=(&x=&y)) POINT=_p_ NOBS=_o_;
		END;
	ELSE
		DO;
			_Found	=	0;
		END;
%MEND	Hop;

Next, I modified the program I shared with you earlier to incorporate the Hop macro.  The code is actually fairly simple and is a single step, a big advantage to the Hop macro.  You could even make it simpler by deleting all my "_Prior" logic and replace it with LAG() based logic.  I personally don't like LAG().  When I've used LAG() with large files, performance has been adversely affected.  Manually setting _Prior type variables is more work, but performance is much better in my experience.

The trick with the program is that I'm doing a look forward not a look back.  I do not read record 3 and try to back update record 2.  That's not typically how SAS works.  Instead, when I read record 2, I read ahead to record 3 and update record 2 in place.  Variables prefixed with "_Next" are from read aheads to the record following the current record.

Here's the program:

DATA	Updated_Drug_Data;
	DROP	_:;
	SET		Drug_Data;

	LENGTH	_Prior_ID		$8;
	LENGTH	_Prior_Drug		$25.;

	LENGTH	_Next_ProdNme	$25.;

	RETAIN	_Prior_ID		' ';
	RETAIN	_Prior_Date		0;
	RETAIN	_Prior_Supp		0;
	RETAIN	_Prior_Drug		' ';

	IF	Patient_ID					=	_Prior_ID												THEN
		DO;
			IF	ProdNme				=	_Prior_Drug												THEN
				DO;
					SvcDate			=	INTNX('DAYS', _Prior_Date, _Prior_Supp);
				END;

			%Hop(Drug_Data, ProdNme, _Next_ProdNme, 1);
			IF	_Found																			THEN
				IF	_Next_ProdNme	IN	("VIVITROL", "REVIA", "NALTREXONE HCL", "NALTREXONE")	THEN
					DO;
						%Hop(Drug_Data, SvcDate, _Next_SvcDate, 1);
						DaySupp		=	INTCK('DAYS', SvcDate, _Next_SvcDate);
					END;
		END;

	_Prior_ID						=	Patient_ID;
	_Prior_Date						=	SvcDate;
	_Prior_Supp						=	DaySupp;
	_Prior_Drug						=	ProdNme;
RUN;

The program takes the input data:

And makes the following changes:  

1. The SvcDate on record 2 is adjusted per the DaySupp on record 1.  The SvcDate on record 2 is set to 7 days after the SvcDate on record 1.  This is conventional processing and does not use the Hop macro.
2. The DaySupp on record 2 is adjusted per the SvcDate on record 3.  I make the update when record 2 is the current record.  I use the Hop macro to read ahead and get a) the ProdName and b) the SvcDate from record 3 while I am processing record 2.  Record 2 is updated based on the data from record 3.  You'll notice that the DaySupp on record two is 3 (not 7).  This is because I updated the SvcDate of record 2 based on the DaySupp of record 1.  If we don't update the SvcDate, then DaySupp on record 2 would be 7 (10/02/2014 to 10/09/2014).  I believe the logic to be correct.

This reflects my understanding of what you're trying to do.  Please correct me if I'm off, and we can adjust the program accordingly.

Jim

[alaxman]

@jimbarbour 

Thank you very much for this thorough explanation, really appreciate it. 

While your code in its entirety did not work, your modified "hop" macro was really key, so thank you! 

This is what worked: 

data pers_3M_12M_30DAY ;
set pers_3M_12M_30DAY;
%Hop1(pers_3M_12M_30DAY, ProdNme, _Next_ProdNme, 1);
			IF	_Found	THEN
				IF	_Next_ProdNme	IN	("VIVITROL", "REVIA", "NALTREXONE HCL", "NALTREXONE")
				THEN 
				%Hop1(pers_3M_12M_30DAY, DAYSUPP_NEW, _Next_DAYSUPP_NEW, 1);
				IF _Next_DAYSUPP_NEW ne . then do;
				daysupp = _Next_DAYSUPP_NEW;
drop _:;
				end;
run;

Many thanks again!

[jimbarbour]

Actually, this is even better than if I had just handed you a solution.  You took the things you needed from my code and arranged them to fulfill your requirements.  Good job!  👍  Far better to understand code advice and apply it than to simply play "monkey see, monkey do."  Hopefully some of this code will come in handy as well for future applications.

Good luck, and glad I was (hopefully) able to pass on a couple of good SAS tips.

Jim

[alaxman]

Needs to generalize to other data

[jimbarbour]

As in the last time we talked, I think the hop macro from @LeonidBatkhan would work well here.  As you read each record, you want to read the daysupp_new variable from the next record.  If daysupp_new is populated, you would want to update daysupp in the current record.

Jim

[Ksharp]

Your Q is ambiguous . better post more data to illustrate .

data have;
infile cards expandtabs ;
input PatientID	svcdate	: $20.daysupp	prodnme : $20.	daysupp_new;
cards;
123	9/29/14	7	ZUBSOLV	.
123	10/2/14	30	ZUBSOLV	.
123	10/9/14	30	NALTREXONE_HCL	7
;

data want;
 set have;
 if _n_=2 then do;
    temp=3;
    set have(keep=daysupp_new rename=(daysupp_new=daysupp)) point=temp;
 end;
 run;

[Kurt_Bremser]

You cannot change a value in a previous observation, but you can change a value in a current observation based on the following observation ("look-ahead"):

data have;
infile datalines dlm=',' dsd truncover;
input Id $ svcdate :mmddyy10. daysupp prodnme :$20. daysupp_new;
format svcdate yymmdd10.;
datalines;
123,9/29/14,7,ZUBSOLV,
123,10/2/14,30,ZUBSOLV,
123,10/9/14,30,NALTREXONE HCL,7
;

data want;
merge
  have
  have (
    firstobs=2
    keep=id daysupp_new
    rename=(id=_id daysupp_new = _dn)
  )
;
if id = _id and _dn ne . then daysupp = _dn;
drop _:;
run;

You can incorporate the logic that sets daysupp_new in this step, and avoid creating that column altogether.