DATA Step, Macro, Functions and more

Set term date based on effective date

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Set term date based on effective date

[ Edited ]

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.

 

NDCNDC_FEETERMED_DATEeffective_date
00002140701$1.8326 02/08/2018
00002140701$1.7970 04/01/2018
00002719001$0.000001/24/201802/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;

 

NDCNDC_FEETERMED_DATEeffective_date
00002140701$1.832603/31/201802/08/2018
00002140701$1.7970 04/01/2018
00002719001$0.000001/24/201802/08/2018
00002759701$20.8000 02/08/2018
00003083050$1.3924 02/08/2018
00003085222$410.673803/31/201802/08/2018
00003085222$430.4023 04/01/2018

 

 


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 10,623

Re: Set term date based on effective date

Posted in reply to sasuser77
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;

View solution in original post


All Replies
PROC Star
Posts: 1,192

Re: Set term date based on effective date

Posted in reply to sasuser77

what does new few mean?

Super User
Posts: 13,084

Re: Set term date based on effective date

Posted in reply to sasuser77

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.

Contributor
Posts: 25

Re: Set term date based on effective date

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.

Super User
Posts: 13,084

Re: Set term date based on effective date

Posted in reply to sasuser77

@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 @draycut 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.

Solution
3 weeks ago
Super User
Posts: 10,623

Re: Set term date based on effective date

Posted in reply to sasuser77
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;
Contributor
Posts: 25

Re: Set term date based on effective date

Perfect. Thanks!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 100 views
  • 0 likes
  • 4 in conversation