BookmarkSubscribeRSS Feed
pankak
Calcite | Level 5

I have a dataset like below.

data have;

input TRAN_ID TRAN_DT date9. amt;

format TRAN_DT date9.;

datalines;

10442673557 01Oct2014 100

10442673557 01Oct2014 200

10442673557 01Nov2014 300

10442673557 01Oct2014 400

10442673557 01Oct2014 500

10442673558 02Oct2014 600

10442673558 02Oct2014 700

10442673558 02Oct2014 400

10442673558 02Oct2014 800

10442673558 02Oct2014 300

10442673558 02Oct2014 100

10442673557 02Dec2014 100

;

i want output like that.

10442673557 01Oct2014 400

10442673558 02Oct2014 100

can anyone please help?

4 REPLIES 4
stat_sas
Ammonite | Level 13

data want;

set have;

by TRAN_ID;

if first.TRAN_ID then id=0;

id+1;

run;

proc sql;

create table final(drop=id) as

select * from want

group by TRAN_ID

having id=max(id)-1;

quit;

Ksharp
Super User
data have;
input TRAN_ID TRAN_DT date9. amt;
format TRAN_DT date9.;
datalines;
 
10442673557 01Oct2014 100
10442673557 01Oct2014 200
10442673557 01Nov2014 300
10442673557 01Oct2014 400
10442673557 01Oct2014 500
10442673558 02Oct2014 600
10442673558 02Oct2014 700
10442673558 02Oct2014 400
10442673558 02Oct2014 800
10442673558 02Oct2014 300
10442673558 02Oct2014 100
10442673558 02Dec2014 100
;
run;
data want(drop=found _TRAN_ID);
 merge have have(keep=TRAN_ID rename=(TRAN_ID=_TRAN_ID) firstobs=3);
 retain found 0;
 if TRAN_ID ne lag(TRAN_ID) then found=0;
 if not found and TRAN_ID ne _TRAN_ID then do;output;found=1;end;
run;


Xia Keshan

naveen_srini
Quartz | Level 8

Hi,

I assume if you are working with transaction dataset just like the way I do here at work, you would have sorted the dataset by id and date and even timestamp if you want to precisely get to see the 2nd last transaction. Anyways, if I understood you correctly, the following code may work for you.

Thanks,

Naveen Srinivasan

l&t infotech

data have;

input TRAN_ID TRAN_DT date9. amt;

format TRAN_DT date9.;

datalines;
10442673557 01Oct2014 100

10442673557 01Oct2014 200

10442673557 01Nov2014 300

10442673557 01Oct2014 400

10442673557 01Oct2014 500

10442673558 02Oct2014 600

10442673558 02Oct2014 700

10442673558 02Oct2014 400

10442673558 02Oct2014 800

10442673558 02Oct2014 300

10442673558 02Oct2014 100

10442673557 02Dec2014 100

;

proc sort data=have out=have2;
by tran_id tran_dt;
run;

data want2;

set have2;

by Tran_id Tran_dt;

array real(3) TRAN_ID TRAN_DT  amt;

array copy(3) tran_id2 tran_dt2 amt2;

retain copy;

if not last.Tran_id then do;

do i=1 to dim(copy);

copy(i)=real(i);

end;

                end;  

if last.Tran_id then output;

keep tran_id2 tran_dt2 amt2;

format tran_dt2 date9.;

run;

naveen_srini
Quartz | Level 8

And if you want to just keep the names, not having to deal with renaming after in my previous:

proc sort data=have out=have2;

by tran_id tran_dt;

run;

data want2;

set have2;

by Tran_id Tran_dt;

array real(3) TRAN_ID TRAN_DT  amt;

array copy(3) tran_id2 tran_dt2 amt2;

retain copy;

if not last.Tran_id then do;

do i=1 to dim(copy);

copy(i)=real(i);

end;

                end;

if last.Tran_id then do;

do i=1 to dim(copy);

real(i)=copy(i);

end;

output;

                end;

keep TRAN_ID TRAN_DT  amt;

run;

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 3163 views
  • 3 likes
  • 4 in conversation