topic Re: Proc SQL max value post baseline in SAS Programming

Proc SQL max value post baseline

Leo9 — Sat, 23 Jun 2018 00:09:48 GMT

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;

Re: Proc SQL max value post baseline

novinosrin — Sat, 23 Jun 2018 00:11:29 GMT

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

Re: Proc SQL max value post baseline

Leo9 — Sat, 23 Jun 2018 00:18:12 GMT

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

Re: Proc SQL max value post baseline

novinosrin — Sat, 23 Jun 2018 00:27:24 GMT

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;

Re: Proc SQL max value post baseline

SASKiwi — Sat, 23 Jun 2018 00:27:46 GMT

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;

Re: Proc SQL max value post baseline

Patrick — Sat, 23 Jun 2018 03:43:10 GMT

@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;

Re: Proc SQL max value post baseline

PGStats — Sat, 23 Jun 2018 21:39:39 GMT

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;

Re: Proc SQL max value post baseline

Leo9 — Mon, 25 Jun 2018 21:08:16 GMT

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;

Re: Proc SQL max value post baseline

PGStats — Tue, 26 Jun 2018 04:16:47 GMT

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.