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
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;
@BrahmanandaRao can you post some sample data?
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, ...
ID AND DATE IS ENOUGH
/* 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;
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;
h.add();
end;
do _n_=1 to 2;
rc = hi.first();
rc = hi.prev();
output;
rc = h.remove();
end;
rc=h.clear();
run;
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;
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.
Ready to level-up your skills? Choose your own adventure.