topic Re: find the highest value in New SAS User

find the highest value

EmadNoureldeen — Sat, 03 Oct 2020 23:29:47 GMT

I want to calculate the maximum value in the year for each code and industry

So my Data is:

year code industry maximum value

2016 001 A 800

2017 001 A 900

2018 001 A 1700

2016 002 A 1200

2017 002 A 1500

2018 002 A 1100

2016 003 B 600

2017 003 B 1400

2018 003 B 950

2016 004 B 780

2017 004 B 1200

2018 004 B 3000

And I want to have:

year code industry maximum value

2016 002 A 1200

2017 002 A 1500

2018 001 A 1700

2016 004 B 780

2017 003 B 1400

2018 004 B 3000

I tried:

Thanks in advance

Re: find the highest value

SAS_Cares — Tue, 06 Oct 2020 13:19:32 GMT

You can find some #SAStips here in computing maximum and minimum values https://blogs.sas.com/content/iml/2014/12/01/max-and-min-rows-and-cols.html You can also find other postings like this one that can provide some extra help https://communities.sas.com/t5/General-SAS-Programming/Fastest-way-to-calculate-the-max-of-a-variable-in-a-large/td-p/247674 Let us know if you still need assistance after looking at these resources.

Re: find the highest value

SASJedi — Wed, 21 Oct 2020 20:16:15 GMT

I would use PROC SQL, like this:

data have;
  infile datalines;
  input year code:$3.  industry:$1. Value;
datalines;
2016  001    A                     800
2017  001    A                     900
2018  001    A                    1700
2016  002    A                    1200
2017  002    A                    1500
2018  002    A                    1100
2016  003    B                    600
2017  003    B                    1400
2018  003    B                    950
2016  004    B                    780
2017  004    B                    1200
2018  004    B                    3000
;

proc sql;
create table want as 
   select Year, Code, Industry, max(Value) as MaxValue
   from have
   group by Year, Code, Industry
;
quit;

Yields:

Want

year	code	industry	MaxValue
2016	001	A	800
2016	002	A	1200
2016	003	B	600
2016	004	B	780
2017	001	A	900
2017	002	A	1500
2017	003	B	1400
2017	004	B	1200
2018	001	A	1700
2018	002	A	1100
2018	003	B	950
2018	004	B	3000

Hope this helps!

Re: find the highest value

PaigeMiller — Wed, 21 Oct 2020 22:24:55 GMT

Use PROC SUMMARY

proc summary data=have nway;
    class industry code year;
    var maximum_value;
    output out=want max=;
run;

Re: find the highest value

hhinohar — Sat, 24 Oct 2020 04:25:29 GMT

*1;
proc sql ;
create table want as
	select year,code,industry,max(maximum_value) as max 
		from have 
			group by industry,year 
				having max(maximum_value)=maximum_value;
quit;