BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9
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.
9 REPLIES 9
Cynthia_sas
SAS Super FREQ
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]
saspert
Pyrite | Level 9
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.
Cynthia_sas
SAS Super FREQ
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
Ksharp
Super User
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
Ksharp
Super User
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
saspert
Pyrite | Level 9
Thank you Ksharp - could you review my short code and see if there are possible errors?

Regards,
saspert
ArtC
Rhodochrosite | Level 12
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.
Ksharp
Super User

Hi. ArtC

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

Ksharp

PaulHomes
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 5960 views
  • 1 like
  • 5 in conversation