Hi,
I have two datasets, the first is a summary table of results from a proc rank for 20 different categories by up to two sub groups into 20 ranks based off a particular score, and the max and min scores in that rank
Category Group Rank Max Min
Y7 Maths 1 0 10
Y7 Maths 2 10 17
...
Y8 English 9 45 52
and so on
. the second is an independent table that has the category and the group on each row, with the continuous score in the same ranges as the max and mins. What I am trying to do is add the RANK column on to the second dataset. so if i had a student in Y7 who in their Maths test scored 5, they would get the RANK value 1. Without coding it in (theres 800 ish rows) i imagine there must be a way to create a format off the dataset that will do it for me but im struggling. Could someone help guide me towards the solution?
Thanks
Perhaps something like this would work for you @denmcl94 ?
It's not using a SAS Format, but I guess it's something like this your looking for.
data rankdata;
input Category $ Group $ Rank Max Min;
datalines;
Y7 Maths 1 0 10
Y7 Maths 2 10 17
Y8 English 9 45 52
;
run;
data independent_table ;
input Category $ Group $ score;
datalines;
Y7 Maths 8
Y7 Maths 14
Y8 English 50
Y7 Maths 2
Y7 Maths 11
Y8 English 45
Y7 Maths 20
Y7 Maths 0
Y8 English 60
;
run;
proc sql;
create table result as
select indep.Category, indep.Group, indep.score
, rankdata.Rank
from independent_table as indep
left join rankdata
on indep.Category = rankdata.Category
and indep.Group = rankdata.Group
and indep.score between rankdata.min and rankdata.max;
quit;
Result dataset
What do all of those columns in that report mean? To make a format you need just one set of ranges. Such as :
proc format ;
value rank 0-10='1' 10-20='2' ... ;
run;
You can then apply this format to numeric value and get the rank of that value.
If you want the rank to be different for different values of CATEGORY and/or GROUP then using formats is probably not the best approach since you would need to make many formats.
Thanks for the response.
I'm trying to avoid coding in the format as the max and mins change for each combination of the first two variables so i was hoping for an easier way of doing it. I could do
If Category="Y7" and Group="Maths" and score between x and y then rank=0;
and so on, but this feels labour intensive. Do you have any other suggestions?
This statement:
if Category="Y7" and Group="Maths" and score between x and y then rank=0;
Is trivial to write from this data:
data have;
input Category $ Group $ Rank Max Min ;
cards;
Y7 Math 1 0 10
Y7 Math 2 10 17
Y8 English 9 45 52
;
With a data step like this:
filename code temp
data _null_;
set have;
file code;
put 'if ' Category= :$quote. 'and ' Group= :$quote.
'and score between ' min 'and ' max 'then ' rank= ';'
;
run;
Which you could then use in data step:
data want;
set new_data ;
%include code ;
run;
PS I corrected the spelling of MATH in your GROUP variable. I have studied many branches of mathematics but only one MATH. Or perhaps I should have change ENGLISH to ENGLISHES instead?
Perhaps something like this would work for you @denmcl94 ?
It's not using a SAS Format, but I guess it's something like this your looking for.
data rankdata;
input Category $ Group $ Rank Max Min;
datalines;
Y7 Maths 1 0 10
Y7 Maths 2 10 17
Y8 English 9 45 52
;
run;
data independent_table ;
input Category $ Group $ score;
datalines;
Y7 Maths 8
Y7 Maths 14
Y8 English 50
Y7 Maths 2
Y7 Maths 11
Y8 English 45
Y7 Maths 20
Y7 Maths 0
Y8 English 60
;
run;
proc sql;
create table result as
select indep.Category, indep.Group, indep.score
, rankdata.Rank
from independent_table as indep
left join rankdata
on indep.Category = rankdata.Category
and indep.Group = rankdata.Group
and indep.score between rankdata.min and rankdata.max;
quit;
Result dataset
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.