topic Wrong ordering or grouping? in SAS Enterprise Guide

Wrong ordering or grouping?

need_some_help — Mon, 19 Sep 2011 07:22:41 GMT

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)

Wrong ordering or grouping?

need_some_help — Mon, 19 Sep 2011 07:36:19 GMT

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

Wrong ordering or grouping?

data_null__ — Mon, 19 Sep 2011 12:24:35 GMT

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

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

Wrong ordering or grouping?

need_some_help — Mon, 19 Sep 2011 15:03:58 GMT

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?

Wrong ordering or grouping?

data_null__ — Mon, 19 Sep 2011 15:20:03 GMT

The ordering is not nested within REGION.

Wrong ordering or grouping?

Tom — Mon, 19 Sep 2011 15:45:16 GMT

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

Wrong ordering or grouping?

need_some_help — Tue, 20 Sep 2011 05:54:49 GMT

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!

Wrong ordering or grouping?

need_some_help — Tue, 20 Sep 2011 06:08:34 GMT

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

Re: Wrong ordering or grouping?

data_null__ — Tue, 20 Sep 2011 12:20:30 GMT

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;

Wrong ordering or grouping?

need_some_help — Fri, 23 Sep 2011 11:23:15 GMT

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!

Wrong ordering or grouping?

need_some_help — Fri, 23 Sep 2011 11:28:50 GMT

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