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 |
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.
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.
Reeza,
I tried that option it didn't give the expected output.
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.
Reeza,
The reason I am creating the new variable(neworder) is I want to order the first six observation and then second six observations.
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?
Reeza,
That is my biggest problem in the data.
Reeza,
Let's Assume this is one group with 19 observations.
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;
Reeza,
this will group the first six observation group 1,group 2......group n.
this want solve my problem.
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.
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 |
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.
I guess I didn't say it...show your code as well.
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.