PROC REPORT Group and Order

[BobHope]

I am 100% sure this one has been already asked, but unfortunately couldn't find any answers to this.

My problem is that I need to group several columns in my PROC REPORT, that is trivial. However I would need to control the order of these columns each individually. I thought that define column / group order=internal would do the trick (my data being ordered as I wish) but to my shocking it does not work. So basically my question is that is it REALLY so that I can't control the ordering of the rows while I am using GROUP in the define statement?

I could also add that I have tried adding these variables as noprint variables and without grouping but it won't have any effect.

Also if I leave out the GROUP from the define statement and just leave the order=internal there then the table is ordered as I was wished. Here is a simple example code to use:

%*Generating some dummy data;
data nonsense;
do i=1 to 3;
var1=i;
do k=1 to 3;
var2=k;
output;
end;
end;
run;

%*Normal proc report with groups no sorting of the data;
title "Table 1";
proc report data=nonsense;
col var1 var2;
define var1/group;
define var2/group;
run;

%*Sorting the data into different order than the default order in grouping;
proc sort data=nonsense;
by var1 descending var2;
run;

%*Printing out the same table as the first one but now with differently sorted data;
title "Same table as the table 1 but now the source data is in different sort";
proc report data=nonsense;
col var1 var2;
define var1/group;
define var2/group;
run;

%*Printing out the table using order=internal option;
title "With Order=internal";
proc report data=nonsense;
col var1 var2;
define var1/group order=internal;
define var2/group order=internal;
run;
title;

[Cynthia_sas · Accepted solution]

Hi, I am not exactly sure of your question. Usage is crucial to understand when you use PROC REPORT. The usages -- DISPLAY, ORDER, GROUP, ANALYSIS SUM, etc -- have an impact on how the item is treated and what happens. So, for example, see what happens in my example #2. So order=internal, for example is only relevant when you have a usage of GROUP or ORDER. It is ignored, not used, etc, when the usage is DISPLAY or ANALYSIS. In my code for #2, I do not have a USAGE specified, so I am taking the default usage for an numeric variable (ANALYSIS) and getting the default statistic of SUM.

  Also note the dataset used for #5 vs #6, #7 -- to use DESCENDING, you do not need a prior sort, just to have the variable usage as GROUP or ORDER. Finally, #8 shows how to get a completely arbitrary order for VAR1 by using a "helper" variable.

cynthia

Here's the code (using your initial data step code to make the data):

*Normal proc report with groups no sorting of the data;
title "1) Only GROUP usage";
proc report data=nonsense;
col var1 var2;
define var1/group;
define var2/group;
run;


*take default usage;
** ORDER=INTERNAL does nothing here;
title "2) Just order=internal and default usage (ANALYSIS SUM for numeric vars)";
title2 "Notice how all rows are collapsed";
proc report data=nonsense;
col var1 var2;
define var1/order=internal;
define var2/order=internal;
run;

*specify ORDER usage;
title "3) Specify ORDER USAGE";
proc report data=nonsense;
col var1 var2;
define var1/order ;
define var2/order ;
run;

*specify DISPLAY usage;
title "4) Specify DISPLAY USAGE";
proc report data=nonsense;
col var1 var2;
define var1/display  ;
define var2/display ;
run;

*Sorting the data into different order than the default order in grouping;
proc sort data=nonsense out=sort_data;
by var1 descending var2;
run;
  
*specify DISPLAY usage to show sort;
title "5) Sorted data with DISPLAY USAGE";
proc report data=sort_data;
col var1 var2;
define var1/display  ;
define var2/display ;
run;

*do not actually need sorting because REPORT has DESCENDING option;
title "6) Original data with ORDER usage and DESCENDING";
proc report data=nonsense;
col var1 var2;
define var1/order ;
define var2/order descending;
run;

*Use GROUP usage with original data and DESCENDING option;
title "7) Original data with GROUP usage and DESCENDING";
proc report data=nonsense;
col var1 var2;
define var1/group ;
define var2/group descending;
run;

** make a new ordering variable for a custom ordering of VAR1;
data neworder;
  set nonsense;
  if var1 = 1 then neword = 2;
  else if var1 = 2 then neword = 3;
  else if var1 = 3 then neword = 1;
run;

title "8) NewOrder variable (hidden with NOPRINT) controls order of VAR1";
proc report data=neworder;
col neword var1 var2;
define neword / order noprint;
define var1/display ;
define var2/display;
run;

And here's the output:

[BobHope]

Thank you Cynthia. The descending option was the missing part from my code. Embarassing that it stood right there in the PROC REPORT manual but I just somehow managed to miss that even I checked the define options.