BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tekish
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

16 REPLIES 16
Reeza
Super User

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.

tekish
Quartz | Level 8

Reeza,

 

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

Reeza
Super User

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.

 

 

tekish
Quartz | Level 8

Reeza,

 

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

Reeza
Super User

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?

tekish
Quartz | Level 8

Reeza,

 

That is my biggest problem in the data.

tekish
Quartz | Level 8

Reeza,

 

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

Reeza
Super User

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;
tekish
Quartz | Level 8

Reeza,

 

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

this want solve my problem.

 

Reeza
Super User

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.

tekish
Quartz | Level 8
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
tekish
Quartz | Level 8
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.

Reeza
Super User

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

tekish
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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