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
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;
PROC SORT DATA = sorted ;
BY DESCENDING sum;
RUN;
DATA want;
SET sorted ;
by DESCENDING sum;
IF _n_<=2 THEN OUTPUT;
RUN;
Here we are:
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;
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;
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.
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;
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!
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.