How to get the 4th and 6th Highest Transaction for each ID and the difference in these transactions

[Hem_ant]

Hi All,

I am new to SAS, want to know how I can get the difference between the 4th highest and 6th highest transaction of each ID

Sample Data: 

data a;
input id transaction;
cards;
1 200
1 300
1 100
1 500
1 600
1 400
1 700
2 300
2 200
2 300
2 400
2 500
2 800
2 900
2 350
3 200
3 150
3 100
3 50
3 400
3 600
3 900
3 450
3 90
;
run;

[gamotte]

Hello,

proc transpose data=a out=ta;
by id;
var transaction;
run;

proc sql noprint;
SELECT count(*) INTO :ncols TRIMMED
FROM dictionary.columns
WHERE LIBNAME="WORK" and MEMNAME="TA" AND substr(NAME,1,3)="COL";
quit;

data want;
set ta;
array tran(*) col&ncols.-col1;
call sortn(of tran(*));
diff=COL4-COL6;

keep id diff;
run;

[Ksharp]

data a;
input id transaction;
cards;
1 200
1 300
1 100
1 500
1 600
1 400
1 700
2 300
2 200
2 300
2 400
2 500
2 800
2 900
2 350
3 200
3 150
3 100
3 50
3 400
3 600
3 900
3 450
3 90
;
run;


ods select none;
ods output  ExtremeValues= ExtremeValues(keep=varname id high LowOrder
where=(LowOrder in (4 6)));
proc univariate data=a nextrval=6;
class id;
var transaction;
run;
ods select all;

[PraneethSrinivas]

proc sort data=a ; by id transaction ; run;

data a_ ;
retain id ;
	set a ;
	by id ;
	if first.id then trans=1 ;
	else trans+1 ;
run;

proc transpose data=a_ out=b prefix=trans_;
	by id ;
	var transaction ;
run;

data b_ (keep=ID trans_4 trans_6 trans_diff) ; 
	set b ;
	trans_diff=(trans_6-trans_4) ;
run;

[Ksharp]

If there were two same transactions in the same id , your code wouldn't work.