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)
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....)
need_some_help wrote:
(it seems like 'others'-rows are 'grouped' somehow....)
You used ORDER=DATA. Reread the description and think about it.
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?
The ordering is not nested within REGION.
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
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!
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;
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!
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..)
It's (I hope - was) not 'sql-way of thinking', just my incorrect way)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.