Solved
Contributor
Posts: 67

# Reorder subrow rownumber

Dear Expert;

I have a huge dataset I want to sort with new variable I created (Neworder). How do I sort the data.

Here is the sample codes;

data want;

set sashelp.class;

rownum=_n_;

newvalue=mod(rownum,6);

if newvalue=0 then newvalue1=6;

else newvalue1=newvalue;

run;

data want1;

set want;

if newvalue1=1 then neworder=1;

if newvalue1=2 then neworder=4;

if newvalue1=3 then neworder=2;

if newvalue1=4 then neworder=5;

if newvalue1=5 then neworder=3;

if newvalue1=6 then neworder=6;

run;

proc sort data=want1 out=test;

by Name rownum neworder;

run;

After running the above code I have the following output.

 Name Sex Age Height Weight rownum newvalue newvalue1 neworder Alfred M 14 69.0 112.5 1 1 1 1 Alice F 13 56.5 84.0 2 2 2 4 Barbara F 13 65.3 98.0 3 3 3 2 Carol F 14 62.8 102.5 4 4 4 5 Henry M 14 63.5 102.5 5 5 5 3 James M 12 57.3 83.0 6 0 6 6 Jane F 12 59.8 84.5 7 1 1 1 Janet F 15 62.5 112.5 8 2 2 4 Jeffrey M 13 62.5 84.0 9 3 3 2 John M 12 59.0 99.5 10 4 4 5 Joyce F 11 51.3 50.5 11 5 5 3 Judy F 14 64.3 90.0 12 0 6 6 Louise F 12 56.3 77.0 13 1 1 1 Mary F 15 66.5 112.0 14 2 2 4 Philip M 16 72.0 150.0 15 3 3 2 Robert M 12 64.8 128.0 16 4 4 5 Ronald M 15 67.0 133.0 17 5 5 3 Thomas M 11 57.5 85.0 18 0 6 6 William M 15 66.5 112.0 19 1 1 1

The  Expected output should be sorted the the first 1-6 then next 1-6 ....

 Name Sex Age Height Weight rownum newvalue newvalue1 neworder Alfred M 14 69.0 112.5 1 1 1 1 Barbara F 13 65.3 98.0 3 3 3 2 Henry M 14 63.5 102.5 5 5 5 3 Alice F 13 56.5 84.0 2 2 2 4 Carol F 14 62.8 102.5 4 4 4 5 James M 12 57.3 83.0 6 0 6 6 Jane F 12 59.8 84.5 7 1 1 1 Jeffrey M 13 62.5 84.0 9 3 3 2 Joyce F 11 51.3 50.5 11 5 5 3 Janet F 15 62.5 112.5 8 2 2 4 John M 12 59.0 99.5 10 4 4 5 Judy F 14 64.3 90.0 12 0 6 6 Louise F 12 56.3 77.0 13 1 1 1 Philip M 16 72.0 150.0 15 3 3 2 Ronald M 15 67.0 133.0 17 5 5 3 Mary F 15 66.5 112.0 14 2 2 4 Robert M 12 64.8 128.0 16 4 4 5 Thomas M 11 57.5 85.0 18 0 6 6 William M 15 66.5 112.0 19 1 1 1

Accepted Solutions
Solution
‎10-23-2017 09:01 AM
Super User
Posts: 21,958

## Re: Reorder subrow rownumber

You need a group variable to keep your groups together. You don't have one now, so you need one for your each group of 6. You can use MOD for this again in conjuction with RETAIN.

Then sort by the GROUP variable and NEWORDER.

All Replies
Super User
Posts: 21,958

## Re: Reorder subrow rownumber

proc sort data=want1 out=test;

by Name rownum neworder;

run;

It's sorting by ROWNUM because you've asked it to. Remove it if you don't want it sorted by ROWNUM anymore.

Contributor
Posts: 67

## Re: Reorder subrow rownumber

Reeza,

I tried that option it didn't give the expected output.

Solution
‎10-23-2017 09:01 AM
Super User
Posts: 21,958

## Re: Reorder subrow rownumber

You need a group variable to keep your groups together. You don't have one now, so you need one for your each group of 6. You can use MOD for this again in conjuction with RETAIN.

Then sort by the GROUP variable and NEWORDER.

Contributor
Posts: 67

## Re: Reorder subrow rownumber

Reeza,

The reason I am creating the new variable(neworder) is I want to order the first six observation and then second six observations.

Super User
Posts: 21,958

## Re: Reorder subrow rownumber

I know...but you don't have a current way of saying which group anything belongs to.

If there's a NEWORDER=1 how do I know if that's for the first group of 6 or the 10th group of 6?

Contributor
Posts: 67

## Re: Reorder subrow rownumber

Reeza,

That is my biggest problem in the data.

Contributor
Posts: 67

## Re: Reorder subrow rownumber

Reeza,

Let's Assume this is one group with 19 observations.

Super User
Posts: 21,958

## Re: Reorder subrow rownumber

Use MOD() to create your groups of 6.

If it was a group of 19 with 1 to 19 that would be different, then it would be just what I suggested earlier, remove the ROWNUM.

``````if mod(_n_, 6) = 1 then group+1;
``````
Contributor
Posts: 67

## Re: Reorder subrow rownumber

Reeza,

this will group the first six observation group 1,group 2......group n.

this want solve my problem.

Super User
Posts: 21,958

## Re: Reorder subrow rownumber

I'm pretty sure it will. Re-read my last few answers of how to use this and your NEWORDER to sort properly.

If it doesn't, show the difference between what you have and what you want.

Contributor
Posts: 67

## Re: Reorder subrow rownumber

 Name Sex Age Height Weight rownum group Alfred M 14 69.0 112.5 1 1 Alice F 13 56.5 84.0 2 1 Barbara F 13 65.3 98.0 3 1 Carol F 14 62.8 102.5 4 1 Henry M 14 63.5 102.5 5 1 James M 12 57.3 83.0 6 1 Jane F 12 59.8 84.5 7 2 Janet F 15 62.5 112.5 8 2 Jeffrey M 13 62.5 84.0 9 2 John M 12 59.0 99.5 10 2 Joyce F 11 51.3 50.5 11 2 Judy F 14 64.3 90.0 12 2 Louise F 12 56.3 77.0 13 3 Mary F 15 66.5 112.0 14 3 Philip M 16 72.0 150.0 15 3 Robert M 12 64.8 128.0 16 3 Ronald M 15 67.0 133.0 17 3 Thomas M 11 57.5 85.0 18 3 William M 15 66.5 112.0 19 4
Contributor
Posts: 67

## Re: Reorder subrow rownumber

[ Edited ]
 Name Sex Age Height Weight rownum newvalue newvalue1 neworder Alfred M 14 69.0 112.5 1 1 1 1 Barbara F 13 65.3 98.0 3 3 3 2 Henry M 14 63.5 102.5 5 5 5 3 Alice F 13 56.5 84.0 2 2 2 4 Carol F 14 62.8 102.5 4 4 4 5 James M 12 57.3 83.0 6 0 6 6 Jane F 12 59.8 84.5 7 1 1 7 Jeffrey M 13 62.5 84.0 9 3 3 8 Joyce F 11 51.3 50.5 11 5 5 9 Janet F 15 62.5 112.5 8 2 2 10 John M 12 59.0 99.5 10 4 4 11 Judy F 14 64.3 90.0 12 0 6 12 Louise F 12 56.3 77.0 13 1 1 13 Philip M 16 72.0 150.0 15 3 3 14 Ronald M 15 67.0 133.0 17 5 5 15 Mary F 15 66.5 112.0 14 2 2 16 Robert M 12 64.8 128.0 16 4 4 17 Thomas M 11 57.5 85.0 18 0 6 18 William M 15 66.5 112.0 19 1 1 19

Is there any way I can re-order like the above output.

Super User
Posts: 21,958

## Re: Reorder subrow rownumber

I guess I didn't say it...show your code as well.

Contributor
Posts: 67

## Re: Reorder subrow rownumber

[ Edited ]

Reeza,

This is the code

data want;

set sashelp.class;

rownum=_n_;

if mod(_n_, 6) = 1 then group+1;

run;

this is the output:

 Name Sex Age Height Weight rownum group Alfred M 14 69.0 112.5 1 1 Alice F 13 56.5 84.0 2 1 Barbara F 13 65.3 98.0 3 1 Carol F 14 62.8 102.5 4 1 Henry M 14 63.5 102.5 5 1 James M 12 57.3 83.0 6 1 Jane F 12 59.8 84.5 7 2 Janet F 15 62.5 112.5 8 2 Jeffrey M 13 62.5 84.0 9 2 John M 12 59.0 99.5 10 2 Joyce F 11 51.3 50.5 11 2 Judy F 14 64.3 90.0 12 2 Louise F 12 56.3 77.0 13 3 Mary F 15 66.5 112.0 14 3 Philip M 16 72.0 150.0 15 3 Robert M 12 64.8 128.0 16 3 Ronald M 15 67.0 133.0 17 3 Thomas M 11 57.5 85.0 18 3 William M 15 66.5 112.0 19 4
☑ This topic is solved.