proc sql;
create table DueDt as
select a.ln_no,a.pmt_am,a.pmt_due_dt
from payment_tran a
left join DSM b
on a.ln_no=b.ln_no
where a.rec_chng_cd in ('A','C')
order by a.ln_no,a.pmt_due_dt;
quit;
Because this hits the entire payment_tran table, this dataset produces 50mllion records. I want only the last transaction based on the ln_no and pmt_due_dt. If it were a datastep I would know how to write this. Because I am joining this to another datastep (DSM) the only way I know to do this is through a proc sql. I want to create the last payment due date table and join it to the DSM table. Any ideas on how to make this query select only the last records using a proc sql statement
You'd better post some sample data and output you need. That would be better . from your description It seems that you need a having clause.
group by ln_no
having pmt_due_dt=max( pmt_due_dt) ;
Ksharp
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.