How to assign values by comparing dates

[knveraraju91]

Dear,

I have two datasets. One EX and other AE. I need create a variable called LASTDOSE in my AE dataset by comparing AEdate with exdate.

My code is working for all observations except where AEDATE and EXDATE are equal and period=1. Please help.

EX

Subid        exdate            exdose       period
1              07FEB2016          5               1
1             17FEB2016         10               2
1              27FEB2016        20               3
2             07JAN2016         20                1
2            17JAN2016          30                2
2            27JAN2016          40                3

3            01 MAR2016        20                1

3            15APR2016          50                 2

3             30APR2016         40                 3

AE

Subid                AEDATE
1                    18FEB2016
2                    17JAN2016

3                    01MAR2016                 

Output needed.

Subid                       AEDATE                           Lastdose
1                               18FEB2016                       10
2                              17JAN2016                         20

3                               01MAR2016                       20            

For SUBID=1    10 is the last dose taken before AEdate 
For SUBID=2     20 is the last dose taken before AEdate (For this subject one  EXDATE, is equal to AEDATE. That dose cannot be taken as per Specs because lastdose=exdose at  exdate should be less than aedate)

For SUBID=3    20 is the dose taken on AEdate (Because AEDATE=EXDATE, my code produces dot for third observation. As per specs if period=1 then last dose=exdose where aedate=exdate can be considered.

Please help in my code

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

[RahulG]

I tried to do the same using datastep. 

If you want to stick with proc sql then you need to revisit your joining condition and may be you would require to use case condition

DATA EX;
INFORMAT exdate DATE9.;

INPUT Subid exdate exdose period;
FORMAT exdate DATE9.;
DATALINES;
1 07FEB2016 5 1
1 17FEB2016 10 2
1 27FEB2016 20 3
2 07JAN2016 20 1
2 17JAN2016 30 2
2 27JAN2016 40 3
3 01MAR2016 20 1
3 15APR2016 50 2
3 30APR2016 40 3
;;

DATA AE;
INFORMAT AEDATE DATE9.;
INPUT Subid AEDATE ;
FORMAT AEDATE DATE9.;
DATALINES;
1 18FEB2016
2 17JAN2016
3 01MAR2016
;;

DATA OUT_PUT(KEEP= SUBID AEDATE LASTDOSE);
MERGE AE (IN=A) EX(IN=B);
RETAIN LASTDOSE;
BY SUBID;
IF AEDATE EQ EXDATE AND PERIOD =1 THEN LASTDOSE=EXDOSE;
IF AEDATE GT EXDATE THEN LASTDOSE=EXDOSE;
IF LAST.SUBID THEN OUTPUT;
RUN;

DATA T3(KEEP= SUBID AEDATE LASTDOSE);
MERGE T2 (IN=A) T1(IN=B);
RETAIN LASTDOSE;
BY SUBID;
IF AEDATE EQ EXDATE AND PERIOD =1 THEN LASTDOSE=EXDOSE;
IF AEDATE GT EXDATE THEN LASTDOSE=EXDOSE;
IF LAST.SUBID THEN OUTPUT;
RUN;

[Kurt_Bremser]

My solution would look like this:

proc sort data=ex;
by subid descending exdate;
run;

data want (keep=subid aedate lastdose);
merge
  ae (in=a)
  ex (in=b)
;
by subid;
if a and b;
retain flag;
if first.subid then flag = 0;
if exdate <= aedate and not flag
then do;
  flag = 1;
  lastdose = exdose;
  output;
end;
run;

The question for me remains:

When do you allow to be exdate equal to aedate, and when must exdate be less than aedate to be considered valid?

[knveraraju91]

Thank you very much for the code. For your question, AEDATE=EXDATE when period =1 becuase with my code lastdose='dot' when aedate=exdate. I will look where I need to modify in your code to get my output.

[Ksharp]

DATA EX;
INFORMAT exdate DATE9.;
INPUT Subid exdate exdose period;
FORMAT exdate DATE9.;
DATALINES;
1 07FEB2016 5 1
1 17FEB2016 10 2
1 27FEB2016 20 3
2 07JAN2016 20 1
2 17JAN2016 30 2
2 27JAN2016 40 3
3 01MAR2016 20 1
3 15APR2016 50 2
3 30APR2016 40 3
;
DATA AE;
INFORMAT AEDATE DATE9.;
INPUT Subid AEDATE ;
FORMAT AEDATE DATE9.;
DATALINES;
1 18FEB2016
2 17JAN2016
3 01MAR2016
;
proc sql;
select a.*,b.exdose 
 from AE as a left join EX as b 
  on a.Subid=b.Subid and a.AEDATE ge b.exdate
   group by a.Subid
    having a.AEDATE-b.exdate=min(a.AEDATE-b.exdate);
quit;

[Ksharp]

Opps.


DATA EX;
INFORMAT exdate DATE9.;
INPUT Subid exdate exdose period;
FORMAT exdate DATE9.;
DATALINES;
1 07FEB2016 5 1
1 17FEB2016 10 2
1 27FEB2016 20 3
2 07JAN2016 20 1
2 17JAN2016 30 2
2 27JAN2016 40 3
3 01MAR2016 20 1
3 15APR2016 50 2
3 30APR2016 40 3
;
DATA AE;
INFORMAT AEDATE DATE9.;
INPUT Subid AEDATE ;
FORMAT AEDATE DATE9.;
DATALINES;
1 18FEB2016
2 17JAN2016
3 01MAR2016
;
proc sql;
select a.*,
coalesce(b.exdose,(select exdose from EX where Subid=a.Subid and exdate=a.AEDATE )) as exdose
 from AE as a left join EX as b 
  on a.Subid=b.Subid and a.AEDATE gt b.exdate
   group by a.Subid
    having a.AEDATE-b.exdate=min(a.AEDATE-b.exdate);
quit;

[RahulG]

@Ksharp Its always a pleasure to read your code.

[knveraraju91]

Thank you very much. Your code worked. But in my dataset there are multiple AE dates for each subject. I am not getting the output all aedates. 

Please look at second OBS. This is OBS is not seen in the output with your code. Thanks. Please help. 

eg:

   sub aedate

1 18FEB2016
1 16FEB2016
2 17JAN2016
3 01MAR2016

Output needed.

Subid                       AEDATE                           Lastdose
1                               18FEB2016                       10

1                               16FEB2016                        5

2                               17JAN2016                         20

3                               01MAR2016                       20  

[Ksharp]

OK. Add one more GROUP variable to make it unique.




DATA EX;
INFORMAT exdate DATE9.;
INPUT Subid exdate exdose period;
FORMAT exdate DATE9.;
DATALINES;
1 07FEB2016 5 1
1 17FEB2016 10 2
1 27FEB2016 20 3
2 07JAN2016 20 1
2 17JAN2016 30 2
2 27JAN2016 40 3
3 01MAR2016 20 1
3 15APR2016 50 2
3 30APR2016 40 3
;
DATA AE;
INFORMAT AEDATE DATE9.;
INPUT Subid AEDATE ;
FORMAT AEDATE DATE9.;
DATALINES;
1 18FEB2016
1 16FEB2016
2 17JAN2016
3 01MAR2016
;
proc sql;
select a.*,
coalesce(b.exdose,(select exdose from EX where Subid=a.Subid and exdate=a.AEDATE )) as exdose
 from AE as a left join EX as b 
  on a.Subid=b.Subid and a.AEDATE gt b.exdate
   group by a.Subid,a.AEDATE 
    having a.AEDATE-b.exdate=min(a.AEDATE-b.exdate);
quit;