BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BobHope
Quartz | Level 8

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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:

 

all_8_reports.png

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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:

 

all_8_reports.png

BobHope
Quartz | Level 8

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.

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 41901 views
  • 4 likes
  • 2 in conversation