topic Re: Calculate min and max values using Proc SQL in SAS Programming

Calculate min and max values using Proc SQL

yoyong555 — Tue, 16 Jun 2020 03:54:21 GMT

Hi everyone.

I have a table below.

Patient_no	Rate_1	Rate_2	Rate_3	Rate_4	Rate_5
1
2
3
4

From the table, I would like to create tables showing the minimum and maximum values for each patient and for each rate.

The tables would look like these:

Patient_no	min_rate	max_rate
1
2
3
4

Rate_no	min_rate	max_rate
1
2
3
4
5

Thank you. Greatly appreciated!

Re: Calculate min and max values using Proc SQL

ChrisNZ — Tue, 16 Jun 2020 04:25:46 GMT

what's the difference between the 2 output tables?

Re: Calculate min and max values using Proc SQL

Kurt_Bremser — Wed, 17 Jun 2020 06:09:34 GMT

First, transpose your dataset to a usable layout:

proc transpose
  data=wide
  out=trans (rename=(col1=rate))
;
by patient_no;
var rate:;
run;

data long;
set trans;
rate_no = input(scan(_name_,2,'_'),best.);
drop _name_;
run;

Then, your 2 queries become very simple:

proc sql;
create table want1 as
  select
    patient_no,
    min(rate) as min_rate,
    max(rate) as max_rate
  from long
  group by patient_no
;
create table want2 as
  select
    rate_no,
    min(rate) as min_rate,
    max(rate) as max_rate
  from long
  group by rate_no
;
quit;

Note: a long dataset layout is always better suited for analysis. See Maxim 19.

Edit: changed the summary functions from sum to min and max.

Re: Calculate min and max values using Proc SQL

andreas_lds — Tue, 16 Jun 2020 06:45:12 GMT

If you don't insist on using proc sql, two simple data-steps and a proc transpose create what you want.

data want1;
   set have;

   min_rate = min(of Rate:);
   max_rate = max(of Rate:);

   drop Rate:;
run;


proc transpose data=have out=transposed;
   var Rate:;
run;

data want2;
   set transposed(drop=_name_);
   
   Rate_No = _n_;
   min_rate = min(of Col:);
   max_rate = max(of Col:);

   drop Col:;
run;

Re: Calculate min and max values using Proc SQL

andreas_lds — Tue, 16 Jun 2020 07:03:51 GMT

Inspired by the dataset "long" - see the answer from @Kurt_Bremser - you could use proc summary twice, imho the proc performs much better than SQL on larger datasets.

proc summary data=long nway;
   class Patient_no;
   var Rate;
   output out=want1(drop= _:) min=min_rate max=max_rate;
run;

proc summary data=long nway;
   class Rate_no;
   var Rate;
   output out=want2(drop= _:) min=min_rate max=max_rate;
run;