Help using Base SAS procedures

"If last" transaction Help Needed

Reply
Contributor
Posts: 59

"If last" transaction Help Needed

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.

Super User
Posts: 17,840

Re: "If last" transaction Help Needed

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

PROC Star
Posts: 1,094

Re: "If last" transaction Help Needed

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;

Super User
Posts: 17,840

Re: "If last" transaction Help Needed

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

Super User
Super User
Posts: 6,502

Re: "If last" transaction Help Needed

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;

Super Contributor
Posts: 578

Re: "If last" transaction Help Needed

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;

Super User
Posts: 5,085

Re: "If last" transaction Help Needed

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.

Ask a Question
Discussion stats
  • 6 replies
  • 197 views
  • 2 likes
  • 6 in conversation