Group sort question

[Chauncy]

Hi, everyone.

I have another sorting question.  I would appreciate it if you could help me.This is the code for a simplified example.

data have;
input ORD FV $ STATE $ SUM;
datalines;
1 apple all 21
2 apple a 7
3 apple b 14
1 carrot all 30
2 carrot a 13
3 carrot b 17
1 banana all 19
2 banana a 4
3 banana b 15
;
run;

 The FV variable shows those fruit or vegetables, STATE variable shows their states. There are only three states, 'a' or 'b'(maybe pieces or juice) or 'all' means both of them. SUM is the variable shows the quantity we have. And I already have the ORD variable for inner sorting. 

I want to sort them using SUM, but group by FV, which means for example, carrot's 'all' is the max SUM and should be the first obs and all of its states('a' 'b') should follow after it as a group settled down on 2 and 3 obs.

The code below is my solution, I need the new order variable SEQ, could it be more easier such as just in one step or do not need split dataset? Really thank you for your reading and thinking.

proc sql noprint;
	create table all as select * from have where state='all' order by sum desc;
quit;
data all;
	set all;
	seq+1;output;
run;
proc sql noprint;
	create table want as
	select a.*,b.seq
	from have as a left join all as b
	on a.fv=b.fv
	;
quit;
proc sort data= want;
	by seq ord;
run;

[PaigeMiller]

In your output data set, why is carrot the first FV group followed by apple followed by banana? This is not in any sorted order. Please explain this part.

[Chauncy]

I need sort fv through sum which state='all' descending, but all states one fv has must followed after it as a group. In fact, 'all' is the summary row of sum for each fv(state='a' or 'b'). Sorry, it maybe not much clear.

[FreelanceReinh · Accepted solution]

Hi @Chauncy,

Thanks to the power of the ORDER BY clause of PROC SQL you can almost always solve your "sort questions" in one step.

proc sql;
create table want as
select a.*
from have a join have(where=(state='all')) b
on a.fv=b.fv
order by b.sum desc, fv, ord;
quit;

You could even get the desired result if variable ORD wasn't there: You would just replace ord by, e.g., whichc(state,'all','a','b').

[Chauncy]

Never used ORDER BY clause while joining tables, there's more to learn. Thanks!

[ballardw]

Maybe show some data before you got the sum.

Proc summary/means with Class variables will create statistics like sum for combinations of the class variables and include a _type_ variable so that the _type_ for the sum of Carrot "all" would be lower than the _type_ of Carrot and State combinations. So use Summary and then sort by FV State _type_.

Or if all of this manipulation is to force records into the order needed for a report you may be doing a lot of extra work as I bet I can make a report that displays in that order (though may use Proc Tabulate depending on actual data and needs).

Beware of oversimplifying examples. An approach that may work for 2 variables may have issues if you are doing multiple "all" levels with more groups.

[Chauncy]

Thank you. I will pay attention for that. This is a simplified model for generating some rtf files during drug clinical trial data submission, such as Summary of Treatment-Emergent Adverse Events by System Organ Class, Preferred Term and Severity, while I need to generate sas datasets with standard specification for easy review. So before using proc report, I also need the datasets already displayed in a certain order with the summary row in it as one observation. For that reason, we can not create any new variables or new statistics during report procedure, and all sorting variables should be prepared before. However, there's more for me to learn.Thanks again.