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 |
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;
what does new few mean?
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.
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.
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.