Proc SQL max value post baseline

[Leo9]

I am trying to use max function within proc sql but not getting desired result. I need the max value of a lab grade post baseline.

Basically, I do not want to consider certain visits (screening and baseline) for max value calculation but it seems the max function is 

is considering all the visit not just visit > 2 (See below). 

proc sql ;
create table test1 as
select *,

case
when toxgr ne '' and visit> 2 
then max(toxgr)
end as gr1

from tlab
group by usubjid,paramcd,lbcat;
quit;

[novinosrin]

Can you please provide  a sample data of what you HAVE and what you WANT?

[Leo9]

Just a made up sample data

Usubjid   Paramcd   Visit   Toxgr  

 5001        ALT           0         3

 5001        ALT           1         3

 5001        ALT           2         2

 5001        ALT           3         1

 5001        ALT           4         2 

I need max(toxgr) = 2 and not 3 

[novinosrin]

data have;
input Usubjid   Paramcd  $  Visit   Toxgr  ;
cards;
 5001        ALT           0         3
 5001        ALT           1         3
 5001        ALT           2         2
 5001        ALT           3         1
 5001        ALT           4         2 
;

proc sql;
create table want(drop=v) as
select *,Visit>2 as v
from have
group by Usubjid,Paramcd,v
having max(Toxgr)=Toxgr and v=1;
quit;

[Patrick]

@Leo9

Building on your initial coding approach below should work.

data have;
  input Usubjid Paramcd $ Visit Toxgr;
  cards;
5001 ALT 0 3
5001 ALT 1 3
5001 ALT 2 2
5001 ALT 3 1
5001 ALT 4 2 
;
run;

proc sql;
  create table test1 as
    select 
      usubjid,
      paramcd,
      max
        (
          case
            when visit> 2 then toxgr
            else .
          end
        ) 
        as gr1
      from have
        group by usubjid,paramcd;
quit;

[PGStats · Accepted solution]

Stating your requirements in te most straightforward way:

data have;
  input Usubjid Paramcd $ lbcat Visit Toxgr;
  cards;
5001 ALT 1 0 3
5001 ALT 1 1 3
5001 ALT 1 2 2
5001 ALT 1 3 1
5001 ALT 1 4 2 
;


proc sql ;
create table want as
select 
    *,
    (   select max(toxgr) from have 
        where usubjid = a.usubjid and 
              paramcd = a. paramcd and
              lbcat = a.lbcat and
              visit > 2  ) as gr1
from have as a;
select * from want;
quit;

[Leo9]

Thank you. What is the purpose of second select statement ( in red below) without that the table will get created with desired result right ? 

proc sql ;
create table want as
select 
    *,
    (   select max(toxgr) from have 
        where usubjid = a.usubjid and 
              paramcd = a. paramcd and
              lbcat = a.lbcat and
              visit > 2  ) as gr1
from have as a;
select * from want;
quit;

[PGStats]

The second select statement defines a so-called correlated query that gets the max toxgr for each row from the main query. It is useful here to impose the additional condition visit > 2.

[SASKiwi]

A sub-query may help here:

proc sql ;
create table test1 as
select A.*
          ,B.toxgr_max
from tlab as A

left join 
(select usubjid
           ,paramcd
           ,lbcat
           ,max(toxgr) as toxgr_max
from tlab
where visit > 2
group by usubjid
              ,paramcd
              ,lbcat
) as B
on A.usubjid = B.usubjid
and A.paramcd = B.paramcd
and A.lbcat = B.lbcat 
;
quit;