How to change the order of rows in the table?

[Sikcion]

Hi guys! I am making a demographic table and have one question about how to change the order of rows in the table?

here is my ideal output:

here is my code:

proc freq data=ADSL noprint;
tables Trt01an *AGegr1 / missing outpct out=AGegr1;
run;
proc freq data=ADSL noprint;
tables trt01an/missing out=AGegr1_n(drop=percent);
run;

data AGegr1_total;
set AGegr1 AGegr1_n;
by trt01an;
keep AGegr1 trt01an count pct_row;
run;

data AGegr1_total(drop=pct_row count);
set AGegr1_total;
by trt01an;
length value$11.;
if AGegr1 ne " " then
value=put(count,3.)|| "(" || put(pct_row,4.1)||"%)";
else if AGegr1 eq " " then do;
value=put(count,3.);
AGegr1="n";
end;
run;

proc sort data=AGegr1_total;
by AGegr1;
run;

proc transpose data=AGegr1_total
out=AGegr1_final (drop=_name_)
prefix=Trt;
by AGegr1;
var value;
id Trt01an;
run;

and my output:

the n row is at the last row, so I want to change it to the first row using ord1 variable, and here is my updated code:

data AGegr1_total(drop=pct_row count);
set AGegr1_total;
by trt01an;
length value$11.;
if AGegr1 ne " " then do;
value=put(count,3.)|| "(" || put(pct_row,4.1)||"%)";
ord1=1;

end;
else if AGegr1 eq " " then do;
value=put(count,3.);
AGegr1="n";
ord1=0;
end;
run;

proc sort data=AGegr1_total;
by ord1 AGegr1;
run;

proc transpose data=AGegr1_total
out=AGegr1_final (drop=_name_)
prefix=Trt;
by AGegr1;
var value;
id Trt01an;
run;

while it have errors:

dataset agegr1_total is not sorted in ascending sequence. The current by group has age group 1 (Char) = n and the next by group has age group 1 (char)=18-40.

Thanks for ur help!

[PaigeMiller]

If you want n at the top of the table, create a sort order variable that indicates the desired sort order (so the row where Agegr1="n" gets sort order 1 and the other rows get appropriate sort orders 2 through 4). Then sort by this sort order variable.

OR

I can't test this, because I don't have your data, but instead of 

set AGegr1 AGegr1_n;

use this

set AGegr1_n AGegr1 ;

[Sikcion]

it still has this error:

while it have errors:

dataset agegr1_total is not sorted in ascending sequence. The current by group has age group 1 (Char) = n and the next by group has age group 1 (char)=18-40.

I think it due to that "n" can't be followed by 18-40, it avoids the ascending sequence rule?

[PaigeMiller]

We don't know what you did. Did you try my two suggestions above?

We can't diagnose your errors unless we see your entire LOG for this portion of the code. Please provide the entire log for this portion of the code (that's lines showing the program statements as the appear in the log, and lines showing errors as they appear in the log) by pasting it as text into the window that appears when you click on the </> icon. DO NOT PROVIDE THE LOG WITHOUT FOLLOWING THESE INSTRUCTIONS.

[Sikcion]

 198        /**Age categorical statistics**/;
 199        proc freq data=ADSL noprint;
 200          tables Trt01an *AGegr1 / missing outpct out=AGegr1;
 201        run;
 
 NOTE: There were 130 observations read from the data set WORK.ADSL.
 NOTE: The data set WORK.AGEGR1 has 9 observations and 6 variables.
 NOTE: PROCEDURE FREQ used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1382.28k
       OS Memory           23216.00k
       Timestamp           01/02/2024 06:46:05 PM
       Step Count                        45  Switch Count  6
       Page Faults                       0
       Page Reclaims                     252
       Page Swaps                        0
       Voluntary Context Switches        29
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           792
       
 
 202        proc freq data=ADSL noprint;
 203        tables trt01an/missing out=AGegr1_n(drop=percent);
 204        run;
 
 NOTE: There were 130 observations read from the data set WORK.ADSL.
 NOTE: The data set WORK.AGEGR1_N has 3 observations and 2 variables.
 NOTE: PROCEDURE FREQ used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.01 seconds
       memory              1124.40k
       OS Memory           22956.00k
       Timestamp           01/02/2024 06:46:05 PM
       Step Count                        46  Switch Count  4
       Page Faults                       0
       Page Reclaims                     191
       Page Swaps                        0
       Voluntary Context Switches        18
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           528
       
 
 205        
 206        data AGegr1_total;
 207        set  AGegr1_n AGegr1;
 208        by trt01an;
 209        keep AGegr1 trt01an count pct_row;
 210        run;
 
 NOTE: There were 3 observations read from the data set WORK.AGEGR1_N.
 NOTE: There were 9 observations read from the data set WORK.AGEGR1.
 NOTE: The data set WORK.AGEGR1_TOTAL has 12 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1380.84k
       OS Memory           22956.00k
       Timestamp           01/02/2024 06:46:05 PM
       Step Count                        47  Switch Count  2
       Page Faults                       0
       Page Reclaims                     179
       Page Swaps                        0
       Voluntary Context Switches        11
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 211        
 212        data AGegr1_total(drop=pct_row count);
 213        set AGegr1_total;
 214        by trt01an;
 215        length value$11.;
 216        if AGegr1 ne " " then do;
 217        value=put(count,3.)|| "(" || put(pct_row,4.1)||"%)";
 218        ord1=1;
 219        end;
 220        else if AGegr1 eq " " then do;
 221        value=put(count,3.);
 222        AGegr1="n";
 223        ord1=0;
 224        end;
 225        run;
 
 NOTE: There were 12 observations read from the data set WORK.AGEGR1_TOTAL.
 NOTE: The data set WORK.AGEGR1_TOTAL has 12 observations and 4 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              1038.59k
       OS Memory           22696.00k
       Timestamp           01/02/2024 06:46:05 PM
       Step Count                        48  Switch Count  2
       Page Faults                       0
       Page Reclaims                     131
       Page Swaps                        0
       Voluntary Context Switches        11
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 226        
 227        proc sort data=AGegr1_total;
 228        by ord1 AGegr1;
 229        run;
 
 NOTE: There were 12 observations read from the data set WORK.AGEGR1_TOTAL.
 NOTE: The data set WORK.AGEGR1_TOTAL has 12 observations and 4 variables.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              942.31k
       OS Memory           22696.00k
       Timestamp           01/02/2024 06:46:05 PM
       Step Count                        49  Switch Count  2
       Page Faults                       0
       Page Reclaims                     116
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           272
       
 
 230        
 231        proc transpose data=AGegr1_total
 232          out=AGegr1_final (drop=_name_)
 233          prefix=Trt;
 234          by AGegr1;
 235          var value;
 236          id Trt01an;
 237        run;
 
 ERROR: Data set WORK.AGEGR1_TOTAL is not sorted in ascending sequence. The current BY group has Age Group 1 (Char) = n and the next 
        BY group has Age Group 1 (Char) = 18 - 40.
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: There were 4 observations read from the data set WORK.AGEGR1_TOTAL.
 WARNING: The data set WORK.AGEGR1_FINAL may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
 NOTE: PROCEDURE TRANSPOSE used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              578.25k
       OS Memory           22436.00k
       Timestamp           01/02/2024 06:46:05 PM
       Step Count                        50  Switch Count  2
       Page Faults                       0
       Page Reclaims                     90
       Page Swaps                        0
       Voluntary Context Switches        11
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           160
       
 238        
 239        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 249        

[PaigeMiller]

In PROC TRANSPOSE use

by ord1 AGegr1;

[tarheel13]

I gave you code for this on your other thread and the rows from my code are in the correct order. typically, people create an order variable to get their rows in the right order and use it in proc report.