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;
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;
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;
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;
If there were two same transactions in the same id , your code wouldn't work.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.