Help in Proc sql code

[knveraraju91]

Dear,

I am using following code.

I got the output I need except  for one subject .

Assuming that the exstdtc is the last dose  given before aesdt. 

In my output I need to have the larger dose taken when two different doses taken  on same day. Please help in my code

one

id                               aesdt

1                              24SEP2014

ex

id                     exstdtc                   exdose

1                    2015-08-28                    3.5

1                    2015-08-28                     7

output needed

id         aesdt                    exdose

1        24SEP2014                 7

proc sql;
create table dose as
select a.*,b.exdose
from one as a left join ex as b
on a.usubjid=b.usubjid and a.AESDT-b.exstdtc >0
group by a.usubjid,a.aesdt
having a.Aesdt-b.exstdtc=min(a.Aesdt-b.exstdtc);
quit;

[PeterClemmensen]

I dont see the variable usubjid in either of your datasets? 🙂

[Astounding]

What should happen if a dose was given on the same day as the adverse event?  (Use that date vs. ignore it?)

[Ksharp]

I would more like to use Data Step.



proc sql;
create table dose as
select usubjid,aesdt,max(exdose) as exdose
from
(
select a.*,b.exdose
from one as a left join ex as b
on a.usubjid=b.usubjid and a.AESDT-b.exstdtc >0
group by a.usubjid,a.aesdt
having a.Aesdt-b.exstdtc=min(a.Aesdt-b.exstdtc)
)
group by usubjid,aesdt;
quit;