BookmarkSubscribeRSS Feed
omega1983
Calcite | Level 5

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.

6 REPLIES 6
Reeza
Super User

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

TomKari
Onyx | Level 15

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;

Reeza
Super User

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

Tom
Super User Tom
Super User

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;

DBailey
Lapis Lazuli | Level 10

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;

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 720 views
  • 2 likes
  • 6 in conversation