The SAS Output Delivery System and reporting techniques

Customizing Sort Order in a proc report

Reply
Super Contributor
Posts: 268

Customizing Sort Order in a proc report

Hello,
I am trying to sort a dataset and display using the proc report in a specific order.

Currently it looks like this (alphabetical order in proc report)-
Distribution Center
Headquarters
Retail Store

It needs to look like this -
HeadQuarters
Distribution Centers
Retail Stores

Please note that it also needs to take care of the difference in the singular/plural words. I have a database which has these values, so a proc sql can extract the 3 values into a dataset.

Appreciate any help on this.

Regards,
saspert.
SAS Super FREQ
Posts: 8,743

Re: Customizing Sort Order in a proc report

Hi:
I can think of 2 possibilities -- if you are certain that the data are in the order you want, then use ORDER=DATA (for your group or order variable). See #1 below.

On the other hand, if you wanted to be absolutely sure that the data were in your order, you could make an ordering variable (see the variable AR_ORD) and then use that variable in your REPORT syntax. See #2 below.

With technique #1, the North area is first. With technique #2, the West area is first. For #2, you can use NOPRINT on the DEFINE statement to "hide" the AR_ORD variable from showing on the final output.

cynthia
[pre]
data myorder;
infile datalines;
input area $ subgrp $ amt;
array tmp{4} $5 _temporary_ ('West' 'South' 'East' 'North');
do i = 1 to 4 by 1;
if tmp(i) = area then ar_ord = i;
end;
return;
datalines;
North AA 100
South AA 100
East AA 100
West AA 100
North AA 100
South AA 100
East AA 100
West AA 100
North BB 100
North BB 100
South BB 150
South BB 150
East AA 175
East BB 175
West AA 200
West BB 200
;
run;

ods html file='c:\temp\certain_order.html' style=sasweb;
proc report data=myorder nowd;
title '1) using ORDER=DATA, North is first';
column area subgrp amt;
define area / group order=data;
define subgrp / group ;
define amt / sum;
run;

proc report data=myorder nowd;
title '2) using special variable, West is first';
column ar_ord area subgrp amt;
define ar_ord/ group /* noprint */;
define area / group;
define subgrp / group ;
define amt / sum;
run;
ods _all_ close;
title;
[/pre]
Super Contributor
Posts: 268

Re: Customizing Sort Order in a proc report

Hi Cynthia,
Thank you for your suggestion. Maybe I overanalyzed the problem initially. I seem to have a simple solution for it - just wanted to run it by you and Ksharp.

DATA BT_PCT;
LENGTH FIRST_LETTER $1;
SET BT_PCT;
FIRST_LETTER=UPCASE(SUBSTR(TRIM(LEFT(BUILDING_TYPE)),1,1));
IF FIRST_LETTER = 'H' THEN BT_SORT_ORDER=1;
IF FIRST_LETTER = 'D' THEN BT_SORT_ORDER=2;
IF FIRST_LETTER = 'R' THEN BT_SORT_ORDER=3;
RUN;


proc report data=bt_pct;
DEFINE BT_SORT_ORDER / GROUP NOPRINT ;
define building_type / display;
(rest of the code....)
run;

Any feedback on this?

Thanks,
saspert.
SAS Super FREQ
Posts: 8,743

Re: Customizing Sort Order in a proc report

Hi:
Your BT_SORT_ORDER variable is like my AR_ORD variable and #2 example. The only difference is that I used an ARRAY to do the lookup and put WEST first and you are using the first letter of your BUILDING_TYPE variable to create the ordering variable.

I tend not to use the leading spaces trick (you can't always be certain that the leading spaces will be respected in all destinations)or the ORDER=DATA trick (the data might not be in the order you want and it might be hard to sort the data to get the order you want). So, I prefer to create an ordering variable that will set the order, but I can hide it with the NOPRINT option.

You have to maintain the code. So you should go with the technique that makes the most sense to you. BTW, if BT_SORT_ORDER is GROUP usage and BUILDING_TYPE is DISPLAY usage, you may as well just change BT_SORT_ORDER to ORDER usage -- because you should be seeing a note in the log to that effect:
[pre]
NOTE: Groups are not created because the usage of BUILDING_TYPE is
DISPLAY. To avoid this note, change all GROUP variables to
ORDER variables.
[/pre]

cynthia
Super User
Posts: 9,682

Re: Customizing Sort Order in a proc report

I did not found problem.
But You can shorten your code like:
[pre]

IF FIRST_LETTER eq: 'H' THEN BT_SORT_ORDER=1;
IF FIRST_LETTER eq: 'D' THEN BT_SORT_ORDER=2;

[/pre]

Ksharp
Super User
Posts: 9,682

Re: Customizing Sort Order in a proc report

I will add the third method.
[pre]



data myorder;
infile datalines;
input department $50.;
datalines;
Distribution Center
Headquarters
Retail Store
;
run;
data myorder;
set myorder;
if department eq 'Headquarters' then department=' '||department;
else if department eq 'Distribution Center' then department=' '||department;
run;
ods html file='c:\temp\certain_order.html' style=sasweb;
proc report data=myorder nowd ;
column department;
define department/order order=internal;
run;
ods html close;
[/pre]



Ksharp
Super Contributor
Posts: 268

Re: Customizing Sort Order in a proc report

Thank you Ksharp - could you review my short code and see if there are possible errors?

Regards,
saspert
Valued Guide
Posts: 632

Re: Customizing Sort Order in a proc report

The NOTSORTED option can be used when building a format. This allows you to have the advantage of a format, but maintain the order of the formats definition.

This can be very useful when trying to both control order and format a column. It can also avoid the need to create an ordering variable.
Super User
Posts: 9,682

Re: Customizing Sort Order in a proc report

Hi. ArtC

Yes, That is a good way too. But I perfer my way ,it is easy and convenient. Excuse me.Smiley Happy

Ksharp

PROC Star
Posts: 392

Re: Customizing Sort Order in a proc report

Hi saspert,

There is another example, using formats and order=internal, that I posted in response to your question on runsubmit.com: http://www.runsubmit.com/questions/592/customized-sorting-in-proc-report

Cheers
Paul
Ask a Question
Discussion stats
  • 9 replies
  • 1891 views
  • 0 likes
  • 5 in conversation