BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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;
2 REPLIES 2
SuryaKiran
Meteorite | Level 14

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;

Capture.PNG

 

Thanks,
Suryakiran
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 771 views
  • 2 likes
  • 3 in conversation