Hi @Ronein ,have you come up with your final answer? I have my solution here (and this is continued from my last thread and step, which was create analysis dataset), today the final step is using proc sql union to produce the final report. I think the code in my last thread(analysis dateset) and this thread(final report) could be the answer to your question. 😀Please kindly let me know if I answer your question, thanks! The code and output are as follows:
/*create separate dataset for
loan1 and loan2, and calculate date
and select rows according to requirement*/
proc sql;
create table want2 as
select custid,
offerdate,
offeramnt,
loandate1,
loansamnt1,
intnx('day',loandate1,-7)
as loand1calc format=date9.
from want1;
select * from want2;
quit;
proc sql;
create table want3 as
select custid,
offerdate,
offeramnt,
loandate2,
loansamnt2,
intnx('day',loandate2,-7)
as loand2calc format=date9.
from want1
where loandate2 ^=. and
loansamnt2 ^=.;
select * from want3;
quit;
data want2a;
set want2;
if offerdate>loand1calc;
run;
proc print data=want2a;run;
data want3a;
set want3;
if offerdate>loand2calc;
run;
proc print data=want3a;run;
/*create final report table
per request using sql subquery and sql union*/
proc sql;
select custid,
offerdate,
offeramnt,
loandate1 as loandate
format=date9.,
loansamnt1 as loansamnt
from want2a
where offeramnt in(select max(offeramnt)
from want2a
group by custid)
union
select custid,
offerdate,
offeramnt,
loandate2 as loandate
format=date9.,
loansamnt2 as loansamnt
from want3a
where offeramnt in(select max(offeramnt)
from want3a
group by custid);
quit;
... View more
