topic Re: How to get the 4th and 6th Highest Transaction for each ID and the difference in these transacti in SAS Programming

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

Hem_ant — Tue, 04 Jun 2019 09:03:18 GMT

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;

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

gamotte — Tue, 04 Jun 2019 09:37:24 GMT

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;

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

Ksharp — Tue, 04 Jun 2019 13:04:07 GMT

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;

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

PraneethSrinivas — Fri, 07 Jun 2019 11:47:13 GMT

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;

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

Ksharp — Sat, 08 Jun 2019 12:50:46 GMT

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