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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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: