topic Re: How to create new variables based on ranking of multiple variables in New SAS User

How to create new variables based on ranking of multiple variables

LimM — Wed, 31 Oct 2018 21:01:08 GMT

Hi all,

I would like to know what SAS codes I can use to decide the values of three ranking variables (Top1 to Top3) based on multiple existing variables (Score_A to F) as below.

Score_A to F are the scores for A, B, C, D, F for each observation. Top1 to 3 are the new variables that I want to create to show among ABCDF, which would go to top1 to top 3 for each observation. Case 1 assigns only one to each of the new variables and Case 2 would assign two if they have the same scores. Thank you in advanced!

Case1

	Top1	Top2	Top3	Score_A	Score_B	Score_C	Score_D	Score_F
obs1	A	B	D	1	0.9	0	0.6	0.6
obs2	D	F	C	0	0.2	0.6	1	1

Case 2

	Top1	Top2	Top3	Score_A	Score_B	Score_C	Score_D	Score_F
obs1	A	B	D F	1	0.9	0	0.6	0.6
obs2	D F	C	B	0	0.2	0.6	1	1

Re: How to create new variables based on ranking of multiple variables

novinosrin — Wed, 31 Oct 2018 21:31:14 GMT

32 Bit machine

data have;
input Score_A	Score_B	Score_C	Score_D	Score_F;
cards;
1	0.9	0	0.6	0.6
0	0.2	0.6	1	1
;

data want;
set have;
array s(*)score_a--score_f;
array top(5) ;
array r[*] top5-top1;
call poke(put(peekc(addr(s(1)),40),40.),addr(top[1]), 40);
call sortn(of r[*]);
/*drop top5 top6;*/
run;

64 Bit Machine

data have;
input Score_A	Score_B	Score_C	Score_D	Score_F;
cards;
1	0.9	0	0.6	0.6
0	0.2	0.6	1	1
;

data want;
set have;
array s(*)score_a--score_f;
array top(5) ;
array r[*] top5-top1;
call pokelong(put(peekclong(addrlong(s(1)),40),40.),addrlong(top[1]), 40);
call sortn(of r[*]);
/*drop top5 top6;*/
run;

Re: How to create new variables based on ranking of multiple variables

novinosrin — Wed, 31 Oct 2018 21:58:01 GMT

HI @LimM Welcome to SAS forum. The above is a little incomplete for the reason you need whichn/vname. Me being too lazy didn't do that part although you will get that from someone. Nonetheless, i encourage you to try the remaining

Re: How to create new variables based on ranking of multiple variables

PGStats — Thu, 01 Nov 2018 02:59:16 GMT

Much easier if you transpose first

data have;
input Obs Score_A	Score_B	Score_C	Score_D	Score_F;
cards;
1 1	0.9	0	0.6	0.6
2 0	0.2	0.6	1	1
;

proc transpose data=have out=scores name=score;
by obs;
var Score_A -- Score_F;
run;

proc sort data=scores; by obs descending col1 score;

data want;
id = 0;
do until(last.obs);
    set scores; by obs descending col1;
    if first.col1 then Id = Id + 1;
    array top{5} $10;
    top{Id} = catx(" ", top{Id}, substr(score, 7, 1));
    end;
keep obs top1-top3;
run;

proc print data=want noobs; run;