DATA Step, Macro, Functions and more

Reorder subrow rownumber

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

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.

NameSexAgeHeightWeightrownumnewvaluenewvalue1neworder
AlfredM1469.0112.51111
AliceF1356.584.02224
BarbaraF1365.398.03332
CarolF1462.8102.54445
HenryM1463.5102.55553
JamesM1257.383.06066
JaneF1259.884.57111
JanetF1562.5112.58224
JeffreyM1362.584.09332
JohnM1259.099.510445
JoyceF1151.350.511553
JudyF1464.390.012066
LouiseF1256.377.013111
MaryF1566.5112.014224
PhilipM1672.0150.015332
RobertM1264.8128.016445
RonaldM1567.0133.017553
ThomasM1157.585.018066
WilliamM1566.5112.019111

 

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

 

NameSexAgeHeightWeightrownumnewvaluenewvalue1neworder
AlfredM1469.0112.51111
BarbaraF1365.398.03332
HenryM1463.5102.55553
AliceF1356.584.02224
CarolF1462.8102.54445
JamesM1257.383.06066
JaneF1259.884.57111
JeffreyM1362.584.09332
JoyceF1151.350.511553
JanetF1562.5112.58224
JohnM1259.099.510445
JudyF1464.390.012066
LouiseF1256.377.013111
PhilipM1672.0150.015332
RonaldM1567.0133.017553
MaryF1566.5112.014224
RobertM1264.8128.016445
ThomasM1157.585.018066
WilliamM1566.5112.019111

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

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.

 

 

View solution in original post


All Replies
Super User
Posts: 19,878

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: 58

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: 19,878

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: 58

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: 19,878

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: 58

Re: Reorder subrow rownumber

Reeza,

 

That is my biggest problem in the data.

Contributor
Posts: 58

Re: Reorder subrow rownumber

Reeza,

 

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

Super User
Posts: 19,878

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: 58

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: 19,878

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: 58

Re: Reorder subrow rownumber

NameSexAgeHeightWeightrownumgroup
AlfredM1469.0112.511
AliceF1356.584.021
BarbaraF1365.398.031
CarolF1462.8102.541
HenryM1463.5102.551
JamesM1257.383.061
JaneF1259.884.572
JanetF1562.5112.582
JeffreyM1362.584.092
JohnM1259.099.5102
JoyceF1151.350.5112
JudyF1464.390.0122
LouiseF1256.377.0133
MaryF1566.5112.0143
PhilipM1672.0150.0153
RobertM1264.8128.0163
RonaldM1567.0133.0173
ThomasM1157.585.0183
WilliamM1566.5112.0194
Contributor
Posts: 58

Re: Reorder subrow rownumber

[ Edited ]
NameSexAgeHeightWeightrownumnewvaluenewvalue1neworder
AlfredM1469.0112.51111
BarbaraF1365.398.03332
HenryM1463.5102.55553
AliceF1356.584.02224
CarolF1462.8102.54445
JamesM1257.383.06066
JaneF1259.884.57117
JeffreyM1362.584.09338
JoyceF1151.350.511559
JanetF1562.5112.582210
JohnM1259.099.5104411
JudyF1464.390.0120612
LouiseF1256.377.0131113
PhilipM1672.0150.0153314
RonaldM1567.0133.0175515
MaryF1566.5112.0142216
RobertM1264.8128.0164417
ThomasM1157.585.0180618
WilliamM1566.5112.0191119

 

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

Super User
Posts: 19,878

Re: Reorder subrow rownumber

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

Contributor
Posts: 58

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:

NameSexAgeHeightWeightrownumgroup
AlfredM1469.0112.511
AliceF1356.584.021
BarbaraF1365.398.031
CarolF1462.8102.541
HenryM1463.5102.551
JamesM1257.383.061
JaneF1259.884.572
JanetF1562.5112.582
JeffreyM1362.584.092
JohnM1259.099.5102
JoyceF1151.350.5112
JudyF1464.390.0122
LouiseF1256.377.0133
MaryF1566.5112.0143
PhilipM1672.0150.0153
RobertM1264.8128.0163
RonaldM1567.0133.0173
ThomasM1157.585.0183
WilliamM1566.5112.0194
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 137 views
  • 3 likes
  • 2 in conversation