Order changed for across variable in proc report

[KK13680]

I am trying to create the table using proc report but the order seems changed but I have no idea why and how to solve it.

Here are my codes:

data test;
infile datalines dlm=",";
informat x $10.
         y $20.
         z $20.
		 group $20.
         description $20.;
input x $ y $ z $ group $  description $;
datalines;
X,A,Total (N=2000),Total,709 (35.5 %)
X,B,Total (N=2000),Total,190 (9.5 %)
X,C,Total (N=2000),Total,2 (0.1 %)
X,A,0 (N=57),Total,23 (40.4 %)
X,B,0 (N=57),Total,4 (7 %)
X,C,0 (N=57),Total,0 (0 %)
X,A,I (N=393),Total,155 (39.4 %)
X,B,I (N=393),Total,29 (7.4 %)
X,C,I (N=393),Total,0 (0 %)
X,A,II (N=551),Total,188 (34.1 %)
X,B,II (N=551),Total,52 (9.4 %)
X,C,II (N=551),Total,0 (0 %)
X,A,Total (N=1520),0,537 (35.3 %)
X,B,Total (N=1520),0,138 (9.1 %)
X,C,Total (N=1520),0,2 (0.1 %)
X,A,0 (N=57),0,23 (40.4 %)
X,B,0 (N=57),0,4 (7 %)
X,C,0 (N=57),0,0 (0 %)
X,A,I (N=349),0,138 (39.5 %)
X,B,I (N=349),0,25 (7.2 %)
X,C,I (N=349),0,0 (0 %)
X,A,II (N=424),0,145 (34.2 %)
X,B,II (N=424),0,42 (9.9 %)
X,C,II (N=424),0,0 (0 %)
X,A,Total (N=480),1,172 (35.8 %)
X,B,Total (N=480),1,52 (10.8 %)
X,C,Total (N=480),1,0 (0%)
X,A,I (N=44),1,17 (38.6 %)
X,B,I (N=44),1,4 (9.1 %)
X,C,I (N=44),1,0 (0%)
X,A,II (N=127),1,43 (33.9 %)
X,B,II (N=127),1,10 (7.9 %)
X,C,II (N=127),1,0 (0%)
;
run;

proc report data=test missing;
column x  y  group, (z, description);
define x/' ' group order=data;
define y/' ' group order=data;
define z/' ' order=data across ;
define group/' ' order=data across nozero;
define description/ ' ' group ;
run;

And the output looks like

But I would like it to be the following where the total one is ahead of the 0 term.

Any solution for solving it?

Thanks!

[Cynthia_sas · Accepted solution]

Hi:

  If you want to use ORDER=DATA, then the data in the DATALINES has to be in the correct order. I restructured your DATALINES a bit and added a COL_ORD variable, so the pattern of the data was a bit more obvious. If you want the Total column to ALWAYS appear before the 0 column, then the data has to be organized that way. Here's what I generated with the changed data and slightly changed code:

  Also, I changed the code in these ways:

1) I never name a variable "GROUP" (or ORDER or COMPUTED, since those are PROC REPORT usages)-- so I changed it to GRP

2) I took off MISSING and NOZERO and used NOCOMPLETECOLS instead

3) a better way to put a character string under an ACROSS item is to put one numeric variable at the end of the report row (in this case, DUMMY) and then use NOPRINT so the dummy values are not visible--but, that way, PROC REPORT has a numeric value to collapse for the GROUP items.

Hope this helps,

Cynthia

The code:

data test;
  length x $10 y $20 grp $20 z $20 description $20;
  infile datalines dlm="," dsd;
 input x $ y $ grp $ z $ description $ col_ord;
datalines;
X,A,Total,Total (N=2000),709 (35.5 %),1.1
X,A,0,Total (N=1520),537 (35.3 %),2.1
X,A,1,Total (N=480),172 (35.8 %),3.1
X,A,Total,0 (N=57),23 (40.4 %),1.2
X,A,0,0 (N=57),23 (40.4 %),2.2
X,A,1,I (N=44),17 (38.6 %),3.2
X,A,Total,I (N=393),155 (39.4 %),1.3
X,A,0,I (N=349),138 (39.5 %),2.3
X,A,1,II (N=127),43 (33.9 %),3.3
X,A,Total,II (N=551),188 (34.1 %),1.4
X,A,0,II (N=424),145 (34.2 %),2.4
X,B,Total,Total (N=2000),190 (9.5 %),1.1
X,B,0,Total (N=1520),138 (9.1 %),2.1
X,B,1,Total (N=480),52 (10.8 %),3.1
X,B,Total,0 (N=57),4 (7 %),1.2
X,B,0,0 (N=57),4 (7 %),2.2
X,B,1,I (N=44),4 (9.1 %),3.2
X,B,Total,I (N=393),29 (7.4 %),1.3
X,B,0,I (N=349),25 (7.2 %),2.3
X,B,1,II (N=127),10 (7.9 %),3.3
X,B,Total,II (N=551),52 (9.4 %),1.4
X,B,0,II (N=424),42 (9.9 %),2.4
X,C,Total,Total (N=2000),2 (0.1 %),1.1
X,C,Total,0 (N=57),0 (0 %),1.2
X,C,Total,I (N=393),0 (0 %),1.3
X,C,Total,II (N=551),0 (0 %),1.4
X,C,0,Total (N=1520),2 (0.1 %),2.1
X,C,0,0 (N=57),0 (0 %),2.2
X,C,0,I (N=349),0 (0 %),2.3
X,C,0,II (N=424),0 (0 %),2.4
X,C,1,Total (N=480),0 (0%),3.1
X,C,1,I (N=44),0 (0%),3.2
X,C,1,II (N=127),0 (0%),3.3
;
run;

title;
proc report data=test nocompletecols;
column x  y  grp,(z, description) dummy;
define x/' ' group  ;
define y/' ' group  ;
define grp/' '  across order=data ;
define z/' '   across order=data;
define description/ ' ' display;
define dummy / computed ' ' noprint;
compute dummy;
  dummy=1;
endcomp;
run;

[KK13680]

Hi Cynthia,

It looks great! But can I know how did you reorganized the data? It seems for data with y=C, the order of grp and z is different with those with y=A or y=B.

Thanks!

[Cynthia_sas]

Yes, that was my mistake in copy and paste, but as you saw, it did not make a difference. Once the "pattern" was set for y=A, PROC REPORT would use that pattern for y=B and y=C -- although in my mind, it would be far better from a maintenance standpoint for the datalines to be consistent.
Sorry, here's the revised code:

data test2;
  length x $10 y $20 grp $20 z $20 description $20;
  infile datalines dlm="," dsd;
 input x $ y $ grp $ z $ description $ col_ord;
datalines;
X,A,Total,Total (N=2000),709 (35.5 %),1.1
X,A,0,Total (N=1520),537 (35.3 %),2.1
X,A,1,Total (N=480),172 (35.8 %),3.1
X,A,Total,0 (N=57),23 (40.4 %),1.2
X,A,0,0 (N=57),23 (40.4 %),2.2
X,A,1,I (N=44),17 (38.6 %),3.2
X,A,Total,I (N=393),155 (39.4 %),1.3
X,A,0,I (N=349),138 (39.5 %),2.3
X,A,1,II (N=127),43 (33.9 %),3.3
X,A,Total,II (N=551),188 (34.1 %),1.4
X,A,0,II (N=424),145 (34.2 %),2.4
X,B,Total,Total (N=2000),190 (9.5 %),1.1
X,B,0,Total (N=1520),138 (9.1 %),2.1
X,B,1,Total (N=480),52 (10.8 %),3.1
X,B,Total,0 (N=57),4 (7 %),1.2
X,B,0,0 (N=57),4 (7 %),2.2
X,B,1,I (N=44),4 (9.1 %),3.2
X,B,Total,I (N=393),29 (7.4 %),1.3
X,B,0,I (N=349),25 (7.2 %),2.3
X,B,1,II (N=127),10 (7.9 %),3.3
X,B,Total,II (N=551),52 (9.4 %),1.4
X,B,0,II (N=424),42 (9.9 %),2.4
X,C,Total,Total (N=2000),2 (0.1 %),1.1
X,C,0,Total (N=1520),2 (0.1 %),2.1
X,C,1,Total (N=480),0 (0%),3.1
X,C,Total,0 (N=57),0 (0 %),1.2
X,C,0,0 (N=57),0 (0 %),2.2
X,C,1,I (N=44),0 (0%),3.2
X,C,Total,I (N=393),0 (0 %),1.3
X,C,0,I (N=349),0 (0 %),2.3
X,C,1,II (N=127),0 (0%),3.3
X,C,Total,II (N=551),0 (0 %),1.4
X,C,0,II (N=424),0 (0 %),2.4
;
run;

title;
proc report data=test2 nocompletecols;
column x  y  grp,(z, description) dummy;
define x/' ' group  ;
define y/' ' group  ;
define grp/' '  across order=data ;
define z/' '   across order=data;
define description/ ' ' display;
define dummy / computed ' ' noprint;
compute dummy;
  dummy=1;
endcomp;
run;

and as you can see the output is the same. FYI -- here's how I patterned the data -- so that the x.1 columns appeared first, followed by the x.2 columns, then the x.3 columns then the x.4 columns.

Cynthia

[KK13680]

Thank you very much. I really appreciate your help!