Proc sql help in my code

[knveraraju91]

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;

[SuryaKiran]

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;

[PGStats]

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;