BookmarkSubscribeRSS Feed
saslove
Quartz | Level 8

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;
2 REPLIES 2
Patrick
Opal | Level 21

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.

Kurt_Bremser
Super User

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1313 views
  • 0 likes
  • 3 in conversation