🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lapis Lazuli | Level 10

## how to get last two lastest transaction dates for each customer id in sas

``````data test;
input id date : date9. x;
format date date9.;
cards;
1 1jan2010 1
1 2jan2010 2
1 2jan2010 32
1 4feb2010 45
1 4feb2010 34
1 4dec2010 45
1 4dec2010 34
2 1jan2010 1
2 2jan2010 2
2 3jan2010 32
2 14feb2010 45
2 24feb2010 34
;
run;``````

Hi

All

how to get last two lastest transaction dates for each customer id in sas

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: how to get last two lastest transaction dates for each customer id in sas

``````data test;
input id date : date9. x;
format date date9.;
cards;
1 1jan2010 1
1 2jan2010 2
1 2jan2010 32
1 4feb2010 45
1 4feb2010 34
1 4dec2010 45
1 4dec2010 34
2 1jan2010 1
2 2jan2010 2
2 3jan2010 32
2 14feb2010 45
2 24feb2010 34
;
run;
proc sort data=test out=temp ;
by id descending date;
run;
data want;
set temp;
by id descending date;
if first.id then n=0;
n+first.date;
if n <= 2;
drop n;
run;
``````
6 REPLIES 6
Tourmaline | Level 20

## Re: how to get last two lastest transaction dates for each customer id in sas

@Anandkvn can you post some sample data?

Meteorite | Level 14

## Re: how to get last two lastest transaction dates for each customer id in sas

How do you want to manage duplicate dates:

Eg. for id = 1, do you want the 2 records with date = 4dec2010? or 4feb2010 and 4dec2010 ?

Do you want to retrieve only the date and the id ? Or also x, ...

Lapis Lazuli | Level 10

## Re: how to get last two lastest transaction dates for each customer id in sas

ID AND DATE IS ENOUGH

Meteorite | Level 14

## Re: how to get last two lastest transaction dates for each customer id in sas

``````
/* Optional -> depends on how you want to manage duplicate records */
proc sort data=test out=test1 (keep= id date) nodupkey;
by id date;
run;

proc transpose data=test1 out=test_tr (drop=_name_) prefix=date;
var date;
by id;
run;

data want;
set test_tr;
format max_date_1 max_date_2 date9.;
max_date_1 = largest(1, of date:);
max_date_2 = largest(2, of date:);
drop date:;
run;

/* Optional -> depends on how you want to get the results */
proc transpose data=want out=want2 (drop=_name_ rename=(col1=date));
var max_date_1 max_date_2;
by id;
run;``````
Tourmaline | Level 20

## Re: how to get last two lastest transaction dates for each customer id in sas

A hash object approach

``````data test;
input id date : date9. x;
format date date9.;
cards;
1 1jan2010 1
1 2jan2010 2
1 2jan2010 32
1 4feb2010 45
1 4feb2010 34
1 4dec2010 45
1 4dec2010 34
2 1jan2010 1
2 2jan2010 2
2 3jan2010 32
2 14feb2010 45
2 24feb2010 34
;
run;

data want(keep=id date);

if _N_=1 then do;
declare hash h (multidata : 'Y', ordered : 'd');
h.definekey('id', 'date');
h.definedone();
declare hiter hi ('h');
end;

do until (last.id);
set test;
by id;
end;

do _n_=1 to 2;
rc = hi.first();
rc = hi.prev();
output;
rc = h.remove();
end;

rc=h.clear();

run;``````
Super User

## Re: how to get last two lastest transaction dates for each customer id in sas

``````data test;
input id date : date9. x;
format date date9.;
cards;
1 1jan2010 1
1 2jan2010 2
1 2jan2010 32
1 4feb2010 45
1 4feb2010 34
1 4dec2010 45
1 4dec2010 34
2 1jan2010 1
2 2jan2010 2
2 3jan2010 32
2 14feb2010 45
2 24feb2010 34
;
run;
proc sort data=test out=temp ;
by id descending date;
run;
data want;
set temp;
by id descending date;
if first.id then n=0;
n+first.date;
if n <= 2;
drop n;
run;
``````
Discussion stats
• 6 replies
• 672 views
• 1 like
• 4 in conversation