topic Re: How do I pull max of multiple columns in SAS? in SAS Programming

How do I pull max of multiple columns in SAS?

kyle234 — Mon, 08 Apr 2024 16:47:37 GMT

I want to loop through all numerical variables and find the max. I would like the results below. Thank you!

gpa no of cars

3 1

4 1

5 2

Results I want

gpa no of cars

5 2

Re: How do I pull max of multiple columns in SAS?

PaigeMiller — Mon, 08 Apr 2024 16:54:39 GMT

This is not a loop. It is PROC SUMMARY or PROC MEANS.

proc means data=have max;
    var gpa no_of_cars;
run;

Re: How do I pull max of multiple columns in SAS?

FreelanceReinh — Mon, 08 Apr 2024 17:21:48 GMT

For even greater simplicity, you can omit the VAR statement in Paige Miller's PROC MEANS step, as the procedure will then analyze all numeric variables in the input dataset.

Re: How do I pull max of multiple columns in SAS?

BrianB4233 — Mon, 08 Apr 2024 18:05:33 GMT

Paige's example will work just fine. Here's a couple more (I added a couple of records on my own):

data have;
input gpa cars;
datalines;
17 4
3 1
7 3
4 1
18 1
5 2
;
run;

** Long and unnecessarily convoluted;
data want1;
set have end=lastrow;

retain gpa_max cars_max; 

if _N_ = 1 then do; /* if 1st record in HAVE ... */
 gpa_max = gpa; /* set GPA_MAX and CARS_MAX to values of GPA & CARS */
 cars_max = cars;
end;

/* if it's not the 1st record .... */
else do;
	if gpa > gpa_max then gpa_max = gpa;
	if cars > cars_max then cars_max = cars;
end;

if lastrow; /* Output if last record in file */

keep gpa_max cars_max;
run;

** Simpler and get the same result;
proc sql;
create table want2 as select
max(gpa) as gpa_max,
max(cars) as cars_max
from have
;
quit;

Re: How do I pull max of multiple columns in SAS?

PaigeMiller — Mon, 08 Apr 2024 20:01:13 GMT

@FreelanceReinh wrote:

For even greater simplicity, you can omit the VAR statement in Paige Miller's PROC MEANS step, as the procedure will then analyze all numeric variables in the input dataset.

Which may or may not be a good thing to do, if there are hundreds of variables and you only want the max of two of the variables, the VAR statement should be used.