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;
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;
Can you please provide a sample data of what you HAVE and what you WANT?
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
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;
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;
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;
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.