topic Re: Customizing Sort Order in a proc report in ODS and Base Reporting

Customizing Sort Order in a proc report

saspert — Tue, 07 Jun 2011 15:40:41 GMT

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.

Re: Customizing Sort Order in a proc report

Cynthia_sas — Tue, 07 Jun 2011 20:03:27 GMT

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]

Re: Customizing Sort Order in a proc report

Ksharp — Wed, 08 Jun 2011 08:55:08 GMT

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

Re: Customizing Sort Order in a proc report

saspert — Wed, 08 Jun 2011 16:44:47 GMT

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.

Re: Customizing Sort Order in a proc report

saspert — Wed, 08 Jun 2011 16:45:46 GMT

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

Regards,
saspert

Re: Customizing Sort Order in a proc report

Cynthia_sas — Wed, 08 Jun 2011 18:52:42 GMT

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

Re: Customizing Sort Order in a proc report

PaulHomes — Wed, 08 Jun 2011 22:30:40 GMT

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

Re: Customizing Sort Order in a proc report

Ksharp — Thu, 09 Jun 2011 02:43:57 GMT

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

Re: Customizing Sort Order in a proc report

ArtC — Wed, 15 Jun 2011 05:16:04 GMT

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.

Re: Customizing Sort Order in a proc report

Ksharp — Tue, 28 Jun 2011 04:53:01 GMT

Hi. ArtC

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

Ksharp