BookmarkSubscribeRSS Feed
need_some_help
Calcite | Level 5

Hi!

I thought I would have no problems with ordering (it has seemed clear to me) but I've got some unexpected result. And can't find reasons why it is going like that. And in consequence of that I get wrong report. Can anyone show me where am I wrong?

Example:

data sales;
input agent$ sale;
datalines;
agent1 1
agent2 4
agent3 3
agent6 1
agent4 2
agent5 7
;
run;

/*need to place certain agents in a special group ('others')
in the end of agent's list sorted in descending order for every region*/
proc sql;
create table for_report as
select
  (case
   when agent in ('agent1','agent2','agent6') then 'city1'
   when agent in ('agent3','agent4','agent5') then 'city2'
   end) as region,
  (case
   when agent in ('agent5','agent6') then 'others'
   else agent
   end) as agent_info,
  sale,
  (case
   when agent in ('agent5','agent6') then -1
   else sale
   end) as  ordsale
from work.sales
order by region,ordsale desc;
quit;


proc report data=for_report nowd;
column region agent_info sale ordsale;
define region / group order=data;
define agent_info / group order=data;
define sale /group order=data;
define ordsale /group order=data /*noprint*/;
run;

After proc sql I get the result I need, but after proc report - unexpected((      (though I'm trying to 'save' proc sql results)

10 REPLIES 10
need_some_help
Calcite | Level 5

After proc sql (and as I need):

REGION AGENT AGENT_INFO SALE ORDSALE

city1      agent2      agent2            4           4
city1      agent1      agent1            1           1
city1      agent6      others            1           -1
city2      agent3      agent3            3           3
city2      agent4      agent4            2           2
city2      agent5      others            7           -1

after proc report:

REGION AGENT_INFO SALE ORDSALE

city1      agent2                 4      4
             agent1                 1      1
             others                  1      -1
city2      others                  7      -1
             agent3                 3      3
             agent4                 2      2

(it seems like 'others'-rows are 'grouped' somehow....)

data_null__
Jade | Level 19

need_some_help wrote:

(it seems like 'others'-rows are 'grouped' somehow....)


You used ORDER=DATA.  Reread the description and think about it.

need_some_help
Calcite | Level 5

The information I found:

The four types of sorts that can be specified with the ORDER= option are:

• ORDER=FORMATTED Sorts by a variable’s formatted values (DEFAULT)

• ORDER=DATA Sorts in the order that the variable values are encountered in the data set                 <---

• ORDER=INTERNAL Sorts by a variable’s unformatted values

• ORDER=FREQ Sorts by frequency counts of the variable values

So I've organised my data in proc sql the way I needed (data set FOR_REPORT) and expected this organization wouldn't change. I can't get my mistake((

Maybe there are more appropriate way to organise data with SAS-tools?

data_null__
Jade | Level 19

The ordering is not nested within REGION.

Tom
Super User Tom
Super User

As data_null_ pointed out your ordering in your SQL select statement and your PROC REPORT are not the same.

In SQL you order by: region,ordsale desc

In your COLUMN statement you order by: region agent_info sale ordsale

Try changing your COLUMN statement to : region ordsale agent_info sale

need_some_help
Calcite | Level 5

I've corrected my code that way and got the results I need (I did correctly? or I'm doing wrong again and needed results prevent me from 'seeing' that?):

proc report data=for_report nowd;

      column region ordsale agent_info sale;

      define region / group order=data;

      define ordsale /display order=data /*noprint*/;

      define agent_info/display;

      define sale /display;

run;

As I've understood (probably my 'oracle-way' of thinking doesn't let me to see the point): when I need ordering by certain variables in my report I should specify an appropriate order in COLUMN-statement and group relevant variables (all ordering-variables except last one). Is that right?

I guess it's very stupid of me not 'to see' that, but I'm trying to put things right. Many thanks for your help!

data_null__
Jade | Level 19

Here is one way to achieve specific ordering for a GROUP variable.   The format option NOTSORTED is important.  Other bits too.

data have;

   input (REGION AGENT AGENT_INFO)($) SALE ORDSALE;

   cards;

city1      agent2      agent2            4           4

city1      agent1      agent1            1           1

city1      agent6      others            1           -1

city2      agent3      agent3            3           3

city2      agent4      agent4            2           2

city2      agent5      others            7           -1

;;;;

   run;

proc format;

   value $ai(notsorted)

      'agent2'='agent2'

      'agent1'='agent1'

      'agent3'='agent3'

      'agent4'='agent4'

      'others'='others'

      ;

     

proc report data=have nowd;

   column region agent_info sale;

   define region / group;

   define agent_info / group order=data format=$ai. preloadfmt;

   define sale / sum;

   run;

need_some_help
Calcite | Level 5

Didn't use formats before (except rather simple cases)... And haven't even thought to get the result I need by that way) But it's very interesting, and very SAS-like)) I'll take note of that, thanks a lot!

need_some_help
Calcite | Level 5

And is the way I've described in 'Sep 20, 2011 1:54 AM'-post incorrect, or unwanted/not optimal for SAS? (to know whether I've understood proc report grouping-ordering concept right..)

need_some_help
Calcite | Level 5

It's (I hope - was) not 'sql-way of thinking', just my incorrect way)

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 10 replies
  • 1129 views
  • 6 likes
  • 3 in conversation