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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.