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;
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').
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.
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').
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.
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: