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!
what's the difference between the 2 output tables?
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.