BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Leo9
Quartz | Level 8

 

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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

Leo9
Quartz | Level 8

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
Tourmaline | Level 20
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
Opal | Level 21

@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
Opal | Level 21

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
Leo9
Quartz | Level 8

 

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
Opal | Level 21

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
SASKiwi
PROC Star

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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