Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Creating a format from a dataset based off a range of values?

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 12-21-2020 10:47 AM
(907 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

How to Concatenate Values

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.