DATA Step, Macro, Functions and more

Proc SQL max value post baseline

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

Proc SQL max value post baseline

 

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;


Accepted Solutions
Solution
3 weeks ago
Esteemed Advisor
Posts: 5,532

Re: Proc SQL max value post baseline

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

View solution in original post


All Replies
PROC Star
Posts: 1,817

Re: Proc SQL max value post baseline

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

Contributor
Posts: 49

Re: Proc SQL max value post baseline

Posted in reply to novinosrin

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 

PROC Star
Posts: 1,817

Re: Proc SQL max value post baseline

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;
Respected Advisor
Posts: 4,736

Re: Proc SQL max value post baseline

@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;
Solution
3 weeks ago
Esteemed Advisor
Posts: 5,532

Re: Proc SQL max value post baseline

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;
PG
Contributor
Posts: 49

Re: Proc SQL max value post baseline

 

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;

 

Esteemed Advisor
Posts: 5,532

Re: Proc SQL max value post baseline

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.

PG
Super User
Posts: 3,919

Re: Proc SQL max value post baseline

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 172 views
  • 1 like
  • 5 in conversation