I would like to rank across multiple grouping variables independently and then combine the results. Is there an easy way to do it (outside of using macros) .
proc RANK data = a ;
by var1;
output out = t1 mean(ret);
run;
proc RANK data = a ;
by var2;
output out = t2 mean(ret);
run;
and then combine t1 and t 2 . ...
The question may be do you want dataset that feeds into some other analysis or is this a report?
if a report some thing like this perhaps:
data have;
infile datalines;
input rownum Rank1 Rank2 Var1;
datalines;
1 1 3 1.2
2 1 4 1.1
3 2 5 0.9
4 3 1 0.766667
5 4 2 0.616667
6 4 1 0.466667
7 4 2 0.316667
8 5 2 0.166667
9 2 1 0.016667
10 4 4 -0.13333
11 5 5 -0.28333
12 4 5 -0.43333
13 3 2 -0.58333
;
run;
data trans (keep=Var1 RankVar RankVal);
set have;
array r rank: ;
do _i_ = 1 to dim(r);
if not missing (r(_i_)) then do;
Rankvar = cat('Rank',_i_);
RankVal = r(_i_);
output;
end;
end;
run;
proc tabulate data=trans;
class RankVar RankVal;
var Var1;
table RankVal='',
RankVar='Rank Variable'*Var1*mean
/box='Ranks'
;
run;
Please post test data (as a datatstep) an drequired output, it really helps illustrate what you want. I imageine its something along the lines of:
VAR1 V1RANK VAR2 V2RANK
ABC 1 DEF 1
ABC 1 RFG 2
GHJ 2 XYZ 1
?
If so you can sort the data, and retain a count for each by group - in the above case:
data want; set have; retain v1rank v2rank; by var1 var2; /* Assumes sorted */ if first.var1 then v1rank=sum(v1rank,1); if first.var2 then v2rank=sum(v2rank,1); run;
Sorry I botched up my request earlier.
Here is the sample data :
rownum | Rank1 | Rank 2 | Var1 |
1 | 1 | 3 | 1.2 |
2 | 1 | 4 | 1.1 |
3 | 2 | 5 | 0.9 |
4 | 3 | 1 | 0.766667 |
5 | 4 | 2 | 0.616667 |
6 | 4 | 1 | 0.466667 |
7 | 4 | 2 | 0.316667 |
8 | 5 | 2 | 0.166667 |
9 | 2 | 1 | 0.016667 |
10 | 4 | 4 | -0.13333 |
11 | 5 | 5 | -0.28333 |
12 | 4 | 5 | -0.43333 |
13 | 3 | 2 | -0.58333 |
Output :
Ranks | Average of Var1 by RAnk1 | Average of Var1 by RAnk2 |
1 | 1.15 | 0.417 |
2 | 0.46 | 0.129 |
3 | 0.09 | 1.2 |
4 | 0.17 | 0.483 |
5 | -0.06 | 0.061 |
Proc rank should read proc means earlier.
hello,
data have;
infile datalines;
input rownum Rank1 Rank2 Var1;
datalines;
1 1 3 1.2
2 1 4 1.1
3 2 5 0.9
4 3 1 0.766667
5 4 2 0.616667
6 4 1 0.466667
7 4 2 0.316667
8 5 2 0.166667
9 2 1 0.016667
10 4 4 -0.13333
11 5 5 -0.28333
12 4 5 -0.43333
13 3 2 -0.58333
;
proc means data=have ;
class Rank1 Rank2;
Var Var1;
ways 1;
output out=int(drop=_type_ _freq_) mean()=;
run;
data want;
set int(drop=rank2 rename=(var1=Average_of_Var1_by_RAnk1 rank1=rank) where=(missing(rank)=0) );
set int(drop=rank1 rename=(var1=Average_of_Var1_by_RAnk2 rank2=rank) where=(missing(rank)=0) );
run;
Thanks it seems to work
But I was wondering if there is a scalable solution ? I have almost 20 Class variables So I would have to repeat this statement 20 times with a drop list that doesn't contain one variable at a time...
set int(drop=rank2 rename=(var1=Average_of_Var1_by_RAnk1 rank1=rank) where=(missing(rank)=0) );
The question may be do you want dataset that feeds into some other analysis or is this a report?
if a report some thing like this perhaps:
data have;
infile datalines;
input rownum Rank1 Rank2 Var1;
datalines;
1 1 3 1.2
2 1 4 1.1
3 2 5 0.9
4 3 1 0.766667
5 4 2 0.616667
6 4 1 0.466667
7 4 2 0.316667
8 5 2 0.166667
9 2 1 0.016667
10 4 4 -0.13333
11 5 5 -0.28333
12 4 5 -0.43333
13 3 2 -0.58333
;
run;
data trans (keep=Var1 RankVar RankVal);
set have;
array r rank: ;
do _i_ = 1 to dim(r);
if not missing (r(_i_)) then do;
Rankvar = cat('Rank',_i_);
RankVal = r(_i_);
output;
end;
end;
run;
proc tabulate data=trans;
class RankVar RankVal;
var Var1;
table RankVal='',
RankVar='Rank Variable'*Var1*mean
/box='Ranks'
;
run;
I think you need make a macro like CALL EXECUTE(): data have; infile datalines; input rownum Rank1 Rank2 Var1; datalines; 1 1 3 1.2 2 1 4 1.1 3 2 5 0.9 4 3 1 0.766667 5 4 2 0.616667 6 4 1 0.466667 7 4 2 0.316667 8 5 2 0.166667 9 2 1 0.016667 10 4 4 -0.13333 11 5 5 -0.28333 12 4 5 -0.43333 13 3 2 -0.58333 ; run; proc sql; create table temp as select rank1 as r,mean(var1) as mean,'rank1' as id length=20 from have group by rank1 union all select rank2 as r,mean(var1) as mean,'rank2' from have group by rank2 order by r; quit; proc transpose data=temp out=want; by r; id id; var mean; run;
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.