proc sql;
create table test1 as
select ln_no,
PMT_TRANS_DT,
pmt_due_dt,
pmt_am,
pmt_trans_cd,
pmt_seq_no
from t_payment_tran
where ln_no in('0275021392')and
pmt_am >0
order by PMT_TRANS_DT ;
quit;
data test2;
set test1;
by PMT_TRANS_DT;
if last.PMT_TRANS_DT then output;
run;
The issue is the datastep called test2. I want to show only the last or most recent transaction based on the PMT_TRANS_DT. The output gives me the same amount of record as the proc data step test1. I should only see 1 row.
run a proc freq on pmt_trans_dt from test1 and see what the values are, they're most likely unique is the issue.
Your code appears to work perfectly to me. Try running the following code. It will create 20 records. Then run your code. Test1 will have 18 records (two records dropped by your criteria), and test2 will have 10 records, with unique dates. This should give you a leg up on debugging.
Tom
data t_payment_tran;
length ln_no $10;
format PMT_TRANS_DT date.;
input ln_no PMT_TRANS_DT date9. pmt_due_dt pmt_am pmt_trans_cd pmt_seq_no;
cards;
0275021392 23Apr2013 65 33 27 35
0275021392 25Apr2013 18 99 24 97
0275021392 20Apr2013 15 48 42 69
0275021392 26Apr2013 67 89 63 93
0275021390 27Apr2013 14 17 45 46
0275021392 21Apr2013 30 15 11 27
0275021392 21Apr2013 18 21 35 1
0275021392 24Apr2013 34 77 73 37
0275021392 22Apr2013 72 2 30 16
0275021392 27Apr2013 64 56 95 41
0275021392 21Apr2013 58 62 50 2
0275021392 19Apr2013 70 61 11 11
0275021392 19Apr2013 74 0 78 43
0275021392 21Apr2013 20 73 37 45
0275021392 25Apr2013 86 84 39 83
0275021392 28Apr2013 37 70 43 45
0275021392 24Apr2013 45 30 58 22
0275021392 27Apr2013 42 90 47 22
0275021392 19Apr2013 20 21 64 56
0275021392 22Apr2013 92 35 65 48
run;
I think you might be looking for last.ln_no rather than last date....
If you want the last observation in the whole input data stream then use the END= option of the SET or MERGE statement.
By statement is not needed, but you might want to keep as SAS will issue an error message if the input is not properly sorted.
data test2;
set test1 end=eof;
by PMT_TRANS_DT;
if eof then output;
run;
You also might get what you need with
proc sql;
create table test1 as
select
t1.ln_no,
t1.PMT_TRANS_DT,
t1.pmt_due_dt,
t1.pmt_am,
t1.pmt_trans_cd,
t1.pmt_seq_no
from
t_payment_tran t1
inner join (select ln_no, max(pmt_trans_dt) as Last_Pmt_Dt from t_payment_tran where pmt_am > 0 group by ln_no) t2
on t1.ln_no=t2.ln_no and t1.pmt_trans_dt = t2.last_pmt_dt
where
t1.ln_no in('0275021392')
and t1.pmt_am >0;
quit;
A fast way, if you need the final observation in the entire data set:
data want;
set have point=_nobs_;
output;
stop;
set have nobs=_nobs_;
run;
Good luck.
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.