topic Re: Finding second last Transaction for each customer id in SAS Data Management

Finding second last Transaction for each customer id

pankak — Mon, 22 Dec 2014 03:54:22 GMT

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?

Re: Finding second last Transaction for each customer id

stat_sas — Mon, 22 Dec 2014 05:33:01 GMT

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;

Re: Finding second last Transaction for each customer id

Ksharp — Mon, 22 Dec 2014 07:43:20 GMT

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

Re: Finding second last Transaction for each customer id

naveen_srini — Mon, 22 Dec 2014 08:37:21 GMT

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;

if last.Tran_id then output;

keep tran_id2 tran_dt2 amt2;

format tran_dt2 date9.;

run;

Re: Finding second last Transaction for each customer id

naveen_srini — Mon, 22 Dec 2014 09:36:22 GMT

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;

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;