Desktop productivity for business analysts and programmers

Wrong ordering or grouping?

Reply
Contributor
Posts: 56

Wrong ordering or grouping?

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)

Contributor
Posts: 56

Wrong ordering or grouping?

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....)

Respected Advisor
Posts: 3,788

Wrong ordering or grouping?

need_some_help wrote:

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


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

Contributor
Posts: 56

Wrong ordering or grouping?

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?

Respected Advisor
Posts: 3,788

Wrong ordering or grouping?

The ordering is not nested within REGION.

Super User
Super User
Posts: 6,845

Wrong ordering or grouping?

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

Contributor
Posts: 56

Wrong ordering or grouping?

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!

Respected Advisor
Posts: 3,788

Re: Wrong ordering or grouping?

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;

Contributor
Posts: 56

Wrong ordering or grouping?

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!

Contributor
Posts: 56

Wrong ordering or grouping?

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..)

Contributor
Posts: 56

Wrong ordering or grouping?

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

Ask a Question
Discussion stats
  • 10 replies
  • 235 views
  • 6 likes
  • 3 in conversation