Set term date based on effective date

[sasuser77]

Hi,

I have following data, where I need to set Termed_Date as effective_date - 1, if there is a new ndc_fee. how do I do it.

NDC	NDC_FEE	TERMED_DATE	effective_date
00002140701	$1.8326		02/08/2018
00002140701	$1.7970		04/01/2018
00002719001	$0.0000	01/24/2018	02/08/2018
00002759701	$20.8000		02/08/2018
00003083050	$1.3924		02/08/2018
00003085222	$410.6738		02/08/2018
00003085222	$430.4023		04/01/2018

Highlighted are the rows for same NDC there is a New_Fee with  new Effective_Date.

I would like to see the result as follow;

NDC	NDC_FEE	TERMED_DATE	effective_date
00002140701	$1.8326	03/31/2018	02/08/2018
00002140701	$1.7970		04/01/2018
00002719001	$0.0000	01/24/2018	02/08/2018
00002759701	$20.8000		02/08/2018
00003083050	$1.3924		02/08/2018
00003085222	$410.6738	03/31/2018	02/08/2018
00003085222	$430.4023		04/01/2018

[PeterClemmensen]

what does new few mean?

[ballardw]

What is the rule for assigning the termed_date. Your description of "effective_date - 1" does not match the values shown on the third row:

00002719001

$0.0000

01/24/2018

02/08/2018

where the "termed_date" is 15 days prior to the effective date.

Is that the data you have? then show the desired result. And it helps to provide data in the form of a data step.

[sasuser77]

I know there are some data discrepancies, however I am only interested in the observations that has new ndc_fee. These observations has to be ignored since it doesn't have new fee.

[ballardw]

---

@sasuser77 wrote:

I know there are some data discrepancies, however I am only interested in the observations that has new ndc_fee. These observations has to be ignored since it doesn't have new fee.

---

As @PeterClemmensen said: Define "new ndc_fee". We do not know what you mean by that.

And it does help to show what the resulting data should look like.

[Ksharp · Accepted solution]

data have;
infile cards expandtabs truncover;
input NDC :$20. NDC_FEE	: dollar32. TERMED_DATE : mmddyy10. effective_date : mmddyy10.;
format TERMED_DATE  effective_date : mmddyy10.;
cards;
00002140701	$1.8326	 .	02/08/2018
00002140701	$1.7970	 .	04/01/2018
00002719001	$0.0000	01/24/2018	02/08/2018
00002759701	$20.8000	. 	02/08/2018
00003083050	$1.3924	 .	02/08/2018
00003085222	$410.6738	 .	02/08/2018
00003085222	$430.4023	. 	04/01/2018
;
run;
data want;
 merge have have(keep=ndc effective_date
rename=(ndc=_ndc effective_date=_date) firstobs=2);
if ndc=_ndc then TERMED_DATE=_date-1;
drop _:;
run;

[sasuser77]

Perfect. Thanks!