Dear ,
Some one helped me yesterday. But for usubjid='3' , I need to write one conditional statement in the 'Having' statement to get max value when dates are equal. I included "b.vsstresn=max(b.vsstresn) and c.vsstresn=max(c.vsstresn).
I got the output I need for usubjid='3', but the usubjid ='4' is not seen in the output. Please suggest. Thank you
output need;
usubjid weightbl heightbl bmibl
1 20 120 13.8
2 40 160 15.6
3 90 190 24.3
4 90 190 24.3
data __ex1;
input usubjid exstdtc $10.;
datalines;
1 2015-02-10
2 2015-03-10
3 2015-04-10
4 2015-04-10
;
data __ex1;
set __ex1;
exstdtcn=input(exstdtc,is8601da.);
format exstdtcn date9.;
run;
data __vs;
input usubjid vstestcd $ vsstresn vsdtc $10.;
datalines;
1 WEIGHT 20 2015-02-09
1 WEIGHT 30 2015-02-19
2 WEIGHT 40 2015-03-09
2 WEIGHT . 2015-03-10
2 WEIGHT 60 2015-03-11
3 WEIGHT 70 2015-04-08
3 WEIGHT 80 2015-04-09
3 WEIGHT 90 2015-04-09
4 WEIGHT 90 2015-04-08
4 WEIGHT 80 2015-04-09
4 WEIGHT 70 2015-04-09
1 HEIGHT 120 2015-02-09
1 HEIGHT 130 2015-02-19
2 HEIGHT 140 2015-03-09
2 HEIGHT . 2015-03-10
2 HEIGHT 160 2015-03-10
3 HEIGHT 170 2015-04-08
3 HEIGHT 180 2015-04-09
3 HEIGHT 190 2015-04-09
4 HEIGHT 190 2015-04-08
4 HEIGHT 180 2015-04-09
4 HEIGHT 170 2015-04-09
;
DATA __VS;
set __vs;
vsdtcn=input(vsdtc,is8601da.);
format vsdtcn date9.;
run;
proc sql;
create table __basevalwh as
select *,(WEIGHTBL/HEIGHTBL/HEIGHTBL)*10000 as BMIBL from (select a.usubjid,b.vsstresn as weightbl,c.vsstresn as heightbl,b.vsdtc as avs,c.vsdtc as cvs
from
__ex1 as a left join
(select * from __vs where vstestcd="WEIGHT" and vsstresn is not missing) as b
on a.usubjid=b.usubjid and a.exstdtcn>= b.vsdtcn left join
(select * from __vs where vstestcd="HEIGHT" and vsstresn is not missing) as c
on a.usubjid=c.usubjid and a.exstdtcn >= c.vsdtcn
group by a.usubjid
having b.vsdtcn=max(b.vsdtcn) and c.vsdtcn=max(c.vsdtcn) and b.vsstresn=max(b.vsstresn) and c.vsstresn=max(c.vsstresn))
;
quit;
How can you expect that output with the condition that won't let.
b.vsdtcn=max(b.vsdtcn) this will exclude the records that have vsstresn=90 which is MAX value for group 4,hence you will not get in output with the condition you have.
proc sql;
select a.*,b.*,c.* ,max(b.vsstresn) as Max_vsstresn,max(c.vsdtcn) format=date9. as max_vsdtcn,max(b.vsdtcn) format=date9. as max_vsdtcn
from __ex1 as a
left join __vs as b
on a.usubjid=b.usubjid and a.exstdtcn>= b.vsdtcn
left join __vs as c
on a.usubjid=c.usubjid and a.exstdtcn >= c.vsdtcn
where b.vstestcd="WEIGHT" and b.vsstresn is not missing and c.vstestcd="HEIGHT" and c.vsstresn is not missing
group by b.usubjid
/* having b.vsdtcn=max(b.vsdtcn) and c.vsdtcn=max(c.vsdtcn) and b.vsstresn=max(b.vsstresn) */
;
quit;
Here is a slightly modified version to account for duplicate dates:
data VSONE;
input ID TCD $ VALUE VDATE :yymmdd10.;
format vdate yymmdd10.;
datalines;
1 WEIGHT 20 2015-02-09
1 WEIGHT 30 2015-02-19
2 WEIGHT 40 2015-03-09
2 WEIGHT . 2015-03-10
2 WEIGHT 60 2015-03-11
3 WEIGHT 70 2015-04-08
3 WEIGHT 80 2015-04-09
3 WEIGHT 90 2015-04-09
4 WEIGHT 90 2015-04-08
4 WEIGHT 80 2015-04-09
4 WEIGHT 70 2015-04-09
1 HEIGHT 120 2015-02-09
1 HEIGHT 130 2015-02-19
2 HEIGHT 140 2015-03-09
2 HEIGHT . 2015-03-10
2 HEIGHT 160 2015-03-10
3 HEIGHT 170 2015-04-08
3 HEIGHT 180 2015-04-09
3 HEIGHT 190 2015-04-09
4 HEIGHT 190 2015-04-08
4 HEIGHT 180 2015-04-09
4 HEIGHT 170 2015-04-09
;
data EXONE;
input ID EDATE :yymmdd10.;
format edate yymmdd10.;
datalines;
1 2015-02-10
2 2015-03-10
3 2015-04-10
4 2015-04-10
;
proc sql;
select
a.id, b.value as weight, c.value as height
from
exone as a left join
(select id, vdate, max(value) as value
from vsone
where tcd="WEIGHT" and value is not missing
group by id, vdate) as b
on a.id=b.id and a.edate>= b.vdate left join
(select id, vdate, max(value) as value
from vsone
where tcd="HEIGHT" and value is not missing
group by id, vdate) as c
on a.id=c.id and a.edate>= c.vdate
group by a.id
having b.vdate=max(b.vdate) and c.vdate=max(c.vdate);
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.