topic Re: how to get the highest profit per each Sub_Category in each year? in SAS Programming

how to get the highest profit per each Sub_Category in each year?

mrahouma — Mon, 09 Dec 2019 03:11:41 GMT

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

Re: how to get the highest profit per each Sub_Category in each year?

Jagadishkatam — Mon, 09 Dec 2019 03:14:35 GMT

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;

Re: how to get the highest profit per each Sub_Category in each year?

mrahouma — Mon, 09 Dec 2019 03:20:58 GMT

Thanks for your reply. Sorry, it doesn't work. Here is screenshot of the resulting data

Re: how to get the highest profit per each Sub_Category in each year?

Jagadishkatam — Mon, 09 Dec 2019 03:32:24 GMT

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;

Re: how to get the highest profit per each Sub_Category in each year?

mrahouma — Mon, 09 Dec 2019 03:48:58 GMT

Thanks. Appreciated