topic Re: PROC SQL max value with corresponding visit in SAS Programming

PROC SQL max value with corresponding visit

saslove — Thu, 06 Feb 2020 00:12:04 GMT

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;

Re: PROC SQL max value with corresponding visit

Patrick — Thu, 06 Feb 2020 02:20:22 GMT

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.

Re: PROC SQL max value with corresponding visit

Kurt_Bremser — Thu, 06 Feb 2020 08:49:54 GMT

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;