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

[Anandkvn]

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

[PeterClemmensen]

@Anandkvn can you post some sample data?

[ed_sas_member]

Hi @Anandkvn 

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, ...

[Anandkvn]

ID AND DATE IS ENOUGH

[ed_sas_member]

/* 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;

[PeterClemmensen]

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;

[Ksharp · Accepted solution]

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;