Quartz | Level 8

## Need to sort the values as per the highest value in col5

Hi Team,

I need to sort the attached data set (attached a data set image) as per the values (Col5)

Actually the sorting needs to be started from ord3. ord 1 and 2 should be as it is.

The requirement is the Ord10 respective  col5 value is 2 So the sorting should be , instead of ord3 and 4. it should be ord 10-12. (ord 11 and 12 is the sub category of ord10)

Also if the col5 values all are equal then it should sort the alphabetic wise

the dataset image

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Need to sort the values as per the highest value in col5

Add some variables to make it possible.  Looks like you need to add a variable to group the types of records and then something to indicate which one is a subgroup.  And if you want to sort the groups by the group total then that also needs to be another variable.  So if you data looks like this and is in some unwanted order:

``````data have ;
infile cards dsd dlm='|' truncover ;
input grp subgrp Label :\$30. Mild Moderate Severe Total ;
if subgrp=1 then grandtotal=total;
retain grandtotal;
cards;
1|1|Number of subjects dosed| | | |6
2|1|Number of subjects with an Event|2|1|0|3
4|1|NervousSystem Disorders|2|0|0|2
4|2|Parasthesia|1|0|0|1
5|1|Gastrointestinal disorders|1|0|0|1
5|2|Vomiting|1|0|0|1
7|1|General Disorder|1|0|0|1
7|2|Chills|1|0|0|1
3|1|Injury,poisoning and other|1|1|0|1
3|2|other|1|1|0|1
6|2|prodeduralpain|1|0|0|1
;``````

You can now sort it to print in the order you want.

``````proc sort data=have ;
by  descending grandtotal grp subgrp descending total;
run;
proc print noobs;
var label  Mild Moderate Severe Total ;
run;
``````
```Label                             Mild    Moderate    Severe    Total

Number of subjects dosed            .         .          .        6
Number of subjects with an Eve      2         1          0        3
NervousSystem Disorders             2         0          0        2
Parasthesia                         1         0          0        1
Injury,poisoning and other          1         1          0        1
other                               1         1          0        1
Gastrointestinal disorders          1         0          0        1
Vomiting                            1         0          0        1
prodeduralpain                      1         0          0        1
General Disorder                    1         0          0        1
Chills                              1         0          0        1```

6 REPLIES 6
Super User

## Re: Need to sort the values as per the highest value in col5

Please supply data in usable form (NO PICTURES, data steps with datalines), and what you expect as result.

Quartz | Level 8

## Re: Need to sort the values as per the highest value in col5

I have a data set as below

 ord Label Mild Moderate Severe Total 1 Number of subjects dosed 6 2 Number of subjects with an Event 2 1 0 3 3 Gastrointestinal disorders 1 0 0 1 4 Vomiting 1 0 0 1 5 General Disorder 1 0 0 1 6 Chills 1 0 0 1 7 Innjury,poisoning and other 1 1 0 1 8 Prodeduralheadache 1 1 0 1 9 prodeduralpain 1 0 0 1 10 NervousSystem Disorders 2 0 0 2 11 Headache 1 0 0 1 12 Parasthesia 1 0 0 1

Here in the column label colored in Red is the heading for the different diseases and down for that is names .

I need to sort the data set in such a way that the label NervousSystem Disordersdisorder total is 2 , so it should come above in the dseas names. so after the ord2 NervousSystem Disorders and the particular names should come...Below is the sample dataset which i need

for better understanding i have marked the changed values in green

 ord Label Mild Moderate Severe Total 1 Number of subjects dosed 6 2 Number of subjects with an Event 2 1 0 3 3 NervousSystem Disorders 2 0 0 2 4 Headache 1 0 0 1 5 Parasthesia 1 0 0 1 6 Gastrointestinal disorders 1 0 0 1 7 Vomiting 1 0 0 1 8 General Disorder 1 0 0 1 9 Chills 1 0 0 1 10 Innjury,poisoning and other 1 1 0 1 11 Prodeduralheadache 1 1 0 1 12 prodeduralpain 1 0 0 1

Super User

## Re: Need to sort the values as per the highest value in col5

``by descending total;``

That will move the entry with a value of 2 up into 3rd position.

But I see no implementable functional rule for the other two you want to move up.

What you are talking about are NOT labels, they are valuesLabel has a completely different meaning in a SAS context. See here

Quartz | Level 8

## Re: Need to sort the values as per the highest value in col5

I have tried already with descending total, but it works only with that particular record, i need to move up the other 2 sub category also with this.. is there any way to create a new sort variable to sort this in such a way
Super User

## Re: Need to sort the values as per the highest value in col5

I see nothing in your data that identifies those two observations as sub-categories to the other.

Super User

## Re: Need to sort the values as per the highest value in col5

Add some variables to make it possible.  Looks like you need to add a variable to group the types of records and then something to indicate which one is a subgroup.  And if you want to sort the groups by the group total then that also needs to be another variable.  So if you data looks like this and is in some unwanted order:

``````data have ;
infile cards dsd dlm='|' truncover ;
input grp subgrp Label :\$30. Mild Moderate Severe Total ;
if subgrp=1 then grandtotal=total;
retain grandtotal;
cards;
1|1|Number of subjects dosed| | | |6
2|1|Number of subjects with an Event|2|1|0|3
4|1|NervousSystem Disorders|2|0|0|2
4|2|Parasthesia|1|0|0|1
5|1|Gastrointestinal disorders|1|0|0|1
5|2|Vomiting|1|0|0|1
7|1|General Disorder|1|0|0|1
7|2|Chills|1|0|0|1
3|1|Injury,poisoning and other|1|1|0|1
3|2|other|1|1|0|1
6|2|prodeduralpain|1|0|0|1
;``````

You can now sort it to print in the order you want.

``````proc sort data=have ;
by  descending grandtotal grp subgrp descending total;
run;
proc print noobs;
var label  Mild Moderate Severe Total ;
run;
``````
```Label                             Mild    Moderate    Severe    Total

Number of subjects dosed            .         .          .        6
Number of subjects with an Eve      2         1          0        3
NervousSystem Disorders             2         0          0        2
Parasthesia                         1         0          0        1
Injury,poisoning and other          1         1          0        1
other                               1         1          0        1
Gastrointestinal disorders          1         0          0        1
Vomiting                            1         0          0        1
prodeduralpain                      1         0          0        1
General Disorder                    1         0          0        1
Chills                              1         0          0        1```

Discussion stats
• 6 replies
• 772 views
• 0 likes
• 3 in conversation