defining before and after dates using changing dates

[Adnan2]

Hi all,

I have the following dataset

data have;

input account_id PT_DT MTHLY_PAY MTHLY_PAY_DT_CHANGE ;

cards;
12345 01FEB2017 456.98 25JUL2016
12345 01FEB2017 480.64 12SEP2016
12345 01FEB2017 512.34 12FEB2017
12345 01FEB2017 476.96 23MAY2018
12346 01APR2020 984.76 15JUN2019
12346 01APR2020 887.91 03APR2020
12436 01APR2020 634.33 04MAR2021
;
run;

and require the following output

ACCOUNT_ID	PT_DT	MTHLY_PAY_BEFORE_PT	MTHLY_PAY_AFTER_PT
12345	01FEB2017	480.64	512.34
12346	01APR2020	984.76	887.91

any recommendations will be well appreciated !

[tarheel13]

Can you try explaining what you want?

[Adnan2]

Sure - 

I have the dataset 'have' and from it I want create an output which lists the MTHLY_PAY value before and after the PT_DT by account id 

for instance for account id 12345 I would require the following output

ACCOUNT_ID	PT_DT	MTHLY_PAY_BEFORE_PT	MTHLY_PAY_AFTER_PT
12345	01FEB2017	480.64	512.34

the value of 480.64 was the last MTHLY_PAY value before the PT_DT of 01FEB2017 and 512.34 was the first value after 01FEB2017

hope this explanation helps 

Regards

Adnan

[tarheel13]

The data step you provided is not inputting correctly. 

[Adnan2]

apologies 

data have;

input account_id PT_DT date9. MTHLY_PAY MTHLY_PAY_DT_CHANGE date9.;
format PT_DT MTHLY_PAY_DT_CHANGE date9.;

cards;
12345 01FEB2017 456.98 25JUL2016
12345 01FEB2017 480.64 12SEP2016
12345 01FEB2017 512.34 12FEB2017
12345 01FEB2017 476.96 23MAY2018
12346 01APR2020 984.76 15JUN2019
12346 01APR2020 887.91 03APR2020
12436 01APR2020 634.33 04MAR2021
;
run;

Regards

Adnan

[tarheel13]

Is the last row a typo? Is it 12436 or 12346 in the last row? 

[tarheel13 · Accepted solution]

This gets what you want. 

data have;
input account_id PT_DT date9. MTHLY_PAY MTHLY_PAY_DT_CHANGE date9.;
format pt_dt mthly_pay_dt_change date9.;
cards;
12345 01FEB2017 456.98 25JUL2016
12345 01FEB2017 480.64 12SEP2016
12345 01FEB2017 512.34 12FEB2017
12345 01FEB2017 476.96 23MAY2018
12346 01APR2020 984.76 15JUN2019
12346 01APR2020 887.91 03APR2020
12436 01APR2020 634.33 04MAR2021
;
run;

data have2;
	set have;
	days_diff=abs(mthly_pay_dt_change - pt_dt);
run;

proc sql;
	create table before as 
	select account_id, pt_dt, mthly_pay as mthly_pay_before_pt
		from have2
		where mthly_pay_dt_change < pt_dt
		group by account_id, pt_dt
		having days_diff=min(days_diff);
		
	create table after as select account_id, pt_dt, mthly_pay as mthly_pay_after_pt
		from have2
		where mthly_pay_dt_change > pt_dt
		group by account_id, pt_dt
		having days_diff=max(days_diff);
		
	create table want as 
	select b.*, a.mthly_pay_after_pt
		from before as b
		join
		after as a
		on b.account_id=a.account_id;
quit;

[Jagadishkatam]

Please try 

data have;
input account_id PT_DT:date9. MTHLY_PAY MTHLY_PAY_DT_CHANGE:date9.;
format PT_DT MTHLY_PAY_DT_CHANGE date9.;
cards;
12345 01FEB2017 456.98 25JUL2016
12345 01FEB2017 480.64 12SEP2016
12345 01FEB2017 512.34 12FEB2017
12345 01FEB2017 476.96 23MAY2018
12346 01APR2020 984.76 15JUN2019
12346 01APR2020 887.91 03APR2020
12346 01APR2020 634.33 04MAR2021
;
run;

 

data want;
set have;
by account_id;
MTHLY_PAY_BEFORE_PT=lag(MTHLY_PAY);
if first.account_id then MTHLY_PAY_BEFORE_PT=.;
if PT_DT<=MTHLY_PAY_DT_CHANGE then flag=1;
MTHLY_PAY_AFTER_PT=MTHLY_PAY;
keep account_id PT_DT MTHLY_PAY_BEFORE_PT MTHLY_PAY_AFTER_PT flag;
run;

proc sort data=want;
by account_id;
where flag=1;
run;

data want;
set want;
by account_id;
if first.account_id;
run;

[Ksharp]

data have;
input account_id PT_DT:date9. MTHLY_PAY MTHLY_PAY_DT_CHANGE:date9.;
format PT_DT MTHLY_PAY_DT_CHANGE date9.;
cards;
12345 01FEB2017 456.98 25JUL2016
12345 01FEB2017 480.64 12SEP2016
12345 01FEB2017 512.34 12FEB2017
12345 01FEB2017 476.96 23MAY2018
12346 01APR2020 984.76 15JUN2019
12346 01APR2020 887.91 03APR2020
12346 01APR2020 634.33 04MAR2021
;
run;
data want;
 if _n_=1 then do;
   if 0 then set have;
   declare hash h();
   h.definekey('MTHLY_PAY_DT_CHANGE');
   h.definedata('MTHLY_PAY');
   h.definedone();
 end;

do until(last.PT_DT );
 set have;
 by  account_id PT_DT ;
 h.add();
 min=min(min,MTHLY_PAY_DT_CHANGE);
 max=max(max,MTHLY_PAY_DT_CHANGE);
end;

do temp=PT_DT to min by -1;
  call missing(MTHLY_PAY);
  if h.find(key:temp)=0 then do;MTHLY_PAY_BEFORE_PT=MTHLY_PAY;leave;end;
end;
do temp=PT_DT to max;
  call missing(MTHLY_PAY);
  if h.find(key:temp)=0 then do;MTHLY_PAY_AFTER_PT=MTHLY_PAY;leave;end;
end;

h.clear();
drop min max temp MTHLY_PAY_DT_CHANGE MTHLY_PAY;
run;