I have the attached database and I want to get table with the highest profit per each Sub_Category in each year?
My data as follow
Sub_Category | order_year | profit |
Paper | 2017 | 178.0125 |
Paper | 2016 | 140.63 |
Paper | 2015 | 106.7725 |
Paper | 2014 | 101.88 |
Art | 2017 | 66.8625 |
Accessories | 2017 | 58.2825 |
Labels | 2017 | 45.0475 |
Art | 2016 | 42.26 |
Art | 2014 | 40.725 |
Labels | 2016 | 40.2075 |
Furnishings | 2017 | 38.5325 |
Art | 2015 | 38.5325 |
Accessories | 2016 | 35.32 |
Accessories | 2014 | 31.8725 |
Labels | 2015 | 31.0475 |
Labels | 2014 | 30.955 |
Phones | 2017 | 30.4325 |
Accessories | 2015 | 29.9775 |
Furnishings | 2016 | 29.345 |
Envelopes | 2015 | 27.39 |
Envelopes | 2017 | 26.8275 |
Envelopes | 2016 | 26.165 |
Phones | 2015 | 26.08916667 |
Furnishings | 2015 | 25.35 |
Any advice will be greatly appreciated
Please try
proc sort data=have ;
by sub_category descending profit order_year ;
run;
data want;
set have;
by sub_category descending profit order_year ;
if first.sub_category;
run;
Please try the below untested code
proc sql;
create table want as select sub_category, order_year, max(profit) as profit
from have group by sub_category, order_year;
quit;
Thanks for your reply. Sorry, it doesn't work. Here is screenshot of the resulting data
Please try
proc sort data=have ;
by sub_category descending profit order_year ;
run;
data want;
set have;
by sub_category descending profit order_year ;
if first.sub_category;
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.