topic Re: "If last" transaction Help Needed in SAS Procedures

"If last" transaction Help Needed

omega1983 — Fri, 19 Apr 2013 14:46:19 GMT

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.

Re: "If last" transaction Help Needed

Reeza — Fri, 19 Apr 2013 14:50:34 GMT

run a proc freq on pmt_trans_dt from test1 and see what the values are, they're most likely unique is the issue.

Re: "If last" transaction Help Needed

TomKari — Fri, 19 Apr 2013 15:30:12 GMT

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;

Re: "If last" transaction Help Needed

Reeza — Fri, 19 Apr 2013 15:31:51 GMT

I think you might be looking for last.ln_no rather than last date....

Re: "If last" transaction Help Needed

Tom — Fri, 19 Apr 2013 17:46:41 GMT

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;

Re: "If last" transaction Help Needed

DBailey — Fri, 19 Apr 2013 18:27:58 GMT

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;

Re: "If last" transaction Help Needed

Astounding — Fri, 19 Apr 2013 19:06:32 GMT

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.