topic Re: sort sum in descending order in SAS Programming

sort sum in descending order

mrahouma — Sun, 08 Dec 2019 14:59:19 GMT

I have big database obtained from store data. I used the folowing code to identify which month has the highest profit

proc sort data=Superstore_Orders1; by order_month  descending profit ; run;
proc means data=Superstore_Orders1 sum ;
output out=sorted (drop= _TYPE_ _FREQ_); 
var   profit ; 
class  order_month ;  run;

I got the following in the results but I want to get the 2 months with highest profit in the output (I think through sorting sum in descending order). Any advice will be graetly appreciated

Re: sort sum in descending order

Jagadishkatam — Sun, 08 Dec 2019 15:25:44 GMT

PROC SORT DATA = sorted ;
    BY DESCENDING sum;
RUN;

DATA want;
    SET sorted ;
    by DESCENDING sum;
    IF _n_<=2 THEN OUTPUT;
RUN;

Re: sort sum in descending order

mrahouma — Sun, 08 Dec 2019 15:32:34 GMT

Thanks for your reply but sorry. This does not work. In log: " ERROR: Variable SUM not found."

Re: sort sum in descending order

Jagadishkatam — Sun, 08 Dec 2019 15:34:26 GMT

ok could you please post the screenshot of the sorted dataset generated from proc means, I want to know the name of the sum variable

Re: sort sum in descending order

mrahouma — Sun, 08 Dec 2019 15:43:44 GMT

Here we are:

Re: sort sum in descending order

Jagadishkatam — Sun, 08 Dec 2019 15:47:25 GMT

Please try below code , here I have subset only the SUM row and considered the first 2 highest sum

PROC SORT DATA = sorted ;
    BY DESCENDING profit;
where _stat_='SUM';
RUN;

DATA want;
    SET sorted ;
    by DESCENDING profit;
    IF _n_<=2 THEN OUTPUT;
RUN;

Re: sort sum in descending order

mrahouma — Sun, 08 Dec 2019 15:54:46 GMT

in _STAT_, there is no "sum" and it gives me empty dataset upon running your code. Is there a way to fix my original code to get sum in the _STAT_ column so I can use your code

Re: sort sum in descending order

Jagadishkatam — Sun, 08 Dec 2019 15:59:05 GMT

Sure please try the below code

proc sort data=Superstore_Orders1; 
by order_month  descending profit ;
 run;

proc means data=Superstore_Orders1 sum ;
output out=sorted (drop= _TYPE_ _FREQ_) sum=sum; 
var   profit ; 
class  order_month ;  
run;

proc sort data=sorted ; 
by  descending sum;
 run;

data want;
set sorted ;
by  descending sum;
if _n_<=2 then output;
run;

Re: sort sum in descending order

mrahouma — Sun, 08 Dec 2019 16:22:21 GMT

Thanks it works. However it gives me 2 rows with the 1st row represents sum of all profit irrespective of month.

I accept the prior post as a solution and would appreciate if you could edit it further.

Re: sort sum in descending order

Jagadishkatam — Sun, 08 Dec 2019 16:25:58 GMT

Sure, please try below code, I included where order_month ne .;

proc sort data=Superstore_Orders1; 
by order_month  descending profit ;
 run;

proc means data=Superstore_Orders1 sum ;
output out=sorted (drop= _TYPE_ _FREQ_) sum=sum; 
var   profit ; 
class  order_month ;  
run;

proc sort data=sorted ; 
by  descending sum;
where order_month ne .;
 run;


data want;
set sorted ;
by  descending sum;
if _n_<=2 then output;
run;