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 !
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;
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
The data step you provided is not inputting correctly.
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
Is the last row a typo? Is it 12436 or 12346 in the last row?
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;
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;
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;
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.