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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.