Hello all,
I have calculated the max value of three variables separately. However, I need the time point associated with the max of these three. How do I calculate that?
For ex: if a subject has 8 values at different time points, I need the max value and it's associate time points for 3 different variables.
proc sql;
create table peak as
select subject, max(cd1922) as peakcd1922, max(cd19) as peakcd19, max(cd22) as peakcd22,
sum(calculated peakcd1922,calculated peakcd19,calculated peakcd22) as peakCarT
from engraft
group by subject;
quit;
Using SQL only something "ugly" as below should do the job.
data have(drop=_:);
length row_id 8;
array var {3} 8.;
array time_pt {3} 8.;
format time_pt: date9.;
do group_id=1 to 3;
do _rows=1 to 100;
do _i=1 to dim(var);
var[_i]=ceil(ranuni(1)*50);
time_pt[_i]=today()-ceil(ranuni(1)*400);
end;
row_id+1;
output;
end;
end;
stop;
run;
proc sql;
create table want as
select
group_id,
max_var1,
(select max(time_pt1) from have s where s.group_id=i.group_id and s.var1=i.max_var1) as
max_time_pt1 format=date9.,
max_var2,
(select max(time_pt2) from have s where s.group_id=i.group_id and s.var2=i.max_var2) as
max_time_pt2 format=date9.,
max_var3,
(select max(time_pt3) from have s where s.group_id=i.group_id and s.var3=i.max_var3) as
max_time_pt3 format=date9.
from
(
select
group_id,
max(var1) as max_var1,
max(var2) as max_var2,
max(var3) as max_var3
from have
group by group_id
) i
;
quit;
/* report showing which row got selected */
proc sql;
select
h.group_id,
h.var1,
h.time_pt1,
w.max_time_pt1,
h.time_pt1=w.max_time_pt1 as sel_ind
from
have h inner join want w
on h.group_id=w.group_id and h.var1=w.max_var1
;
quit;
If you're dealing with high data volumes then using a different approach will likely perform better.
With a data step:
data have (drop=_:);
length
subject 8
time_pt 4
;
format time_pt yymmddd10.;
array var {3} 8.;
do subject = 1 to 3;
do _rows = 1 to 100;
time_pt = today()-ceil(ranuni(1)*400);
do _i = 1 to dim(var);
var[_i]=ceil(ranuni(1)*50);
end;
output;
end;
end;
run;
data want;
set have;
by subject;
retain peak1-peak3 date1-date3;
if first.subject
then call missing(of peak:,of date:);
array peaks {3} peak:;
array dates {3} date:;
array vars {3} var:;
format date: yymmddd10.;
do _i = 1 to 3;
if vars{_i} > peaks{_i}
then do;
peaks{_i} = vars{_i};
dates{_i} = time_pt;
end;
end;
if last.subject;
drop time_pt var: _:;
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.