Creating a format from a dataset based off a range of values?

[denmcl94]

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

[Tom]

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.

[denmcl94]

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?

[Tom]

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? 

[GertNissen · Accepted solution]

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

[denmcl94]

Thank you very much for this @GertNissen. I wasn't sure if formats were the best way to do it but your solution was exactly what i'm looking for