topic Re: Reorder subrow rownumber in SAS Programming

Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 17:53:40 GMT

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

Re: Reorder subrow rownumber

Reeza — Fri, 20 Oct 2017 17:56:16 GMT

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.

Re: Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 18:00:25 GMT

Reeza,

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

Re: Reorder subrow rownumber

Reeza — Fri, 20 Oct 2017 18:02:27 GMT

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.

Re: Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 18:04:40 GMT

Reeza,

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

Re: Reorder subrow rownumber

Reeza — Fri, 20 Oct 2017 18:05:52 GMT

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?

Re: Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 18:56:45 GMT

Reeza,

That is my biggest problem in the data.

Re: Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 18:58:51 GMT

Reeza,

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

Re: Reorder subrow rownumber

Reeza — Fri, 20 Oct 2017 19:00:42 GMT

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;

Re: Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 19:09:56 GMT

Reeza,

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

this want solve my problem.

Re: Reorder subrow rownumber

Reeza — Fri, 20 Oct 2017 19:12:04 GMT

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.

Re: Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 19:12:14 GMT

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

Re: Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 19:20:27 GMT

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.

Re: Reorder subrow rownumber

Reeza — Fri, 20 Oct 2017 19:19:40 GMT

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

Re: Reorder subrow rownumber

tekish — Fri, 20 Oct 2017 19:24:10 GMT

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

Re: Reorder subrow rownumber

Reeza — Fri, 20 Oct 2017 19:32:42 GMT

Where's the rest of the code? The sort and the row/group numbers as well?

Re: Reorder subrow rownumber

tekish — Mon, 23 Oct 2017 12:12:54 GMT

Reeza,

It works when I added all the codes. Thank you for you help.

here is the final code.

data want;

set sashelp.class;

rownum=_n_;

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

newvalue=mod(rownum,6);

if newvalue=0 then newvalue1=6;

else newvalue1=newvalue;

run;

data want1(drop=newvalue newvalue1);

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 group neworder;

run;

The expected output.

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