How to assign values by comparing dates in two data sets

[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.

Some one from helped me previously. But I need to fix one more thing in that code. Please help.

EX

Subid        exdate            exdose                    excat
1              07FEB2016          5                        PSG night
1             17FEB2016         10                      Study drug first taken                    
1              27FEB2016        20
2             07JAN2016         20                        PSG night
2            17JAN2016          30                      Study drug first taken
2           17JAN2016           40

AE

Subid                AEDATE
1                    18FEB2016
2                    11JAN2016

Output needed.

Subid                       AEDATE                           Lastdose
1                               18FEB2016                       10
2                              11JAN2016                          .

For SUBID=1 10 is the last dose taken before AEdate 
For SUBID=2,  the last dose taken before AEdate is blank because study drug date first taken(17JAN2016) has to be considered. So, the last dose is blank.

I need to consider the doses taken on or after excat='study first drug taken'..

Please help in my code 

Ouput by my code;

Subid                       AEDATE                           Lastdose
1                               18FEB2016                       10
2                              11JAN2016                         20

proc sql;
select a.*,b.exdose
 from AE as a left join EX as b 
  on a.Subid=b.Subid and a.AEDATE-b.exdate >0
    group by a.Subid,a.AEDATE 
     having a.AEDATE-b.exdate=min(a.AEDATE-b.exdate);
quit; 

[Jagadishkatam]

Hi,

Please try to retain the first dose date related to study in a new variable and then back merge that dataset with the source EX dataset on subid.

data firstdose;
set ex (where=(excat='Study drug first taken'));
by subid;
if first.subid;
rename exdate=firstdosdt;
keep Subid exdate;
run;

data ex_;
merge ex(in=a) firstdose(in=b);
by subid;
if a;
run;

Then , use this dataset in the proc sql as below

proc sql;
select a.*,b.exdose
 from AE as a left join EX_ as b 
  on a.Subid=b.Subid and a.AEDATE-b.firstdosdt >0
    group by a.Subid,a.AEDATE 
     having b.firstdosdt=b.exdate and a.AEDATE-b.firstdosdt=min(a.AEDATE-b.firstdosdt) ;
quit;

Hope this helps.

[knveraraju91]

Thank you for your help. Your code gives the 'first study dose taken'  dose values for all observation.  But I need most recent dose taken prior to aedate.

I modified your code. Please check if this is ok and let me know if anything wrong. Thanks

proc sql;
create table one as
select a.*,b.exdose,exdate,firstdosdt
from AE as a left join EX_ as b
on a.Subid=b.Subid and a.AEDATE-b.firstdosdt >0 and a.AEDATE-b.exdate >0
group by a.Subid,a.AEDATE
having a.AEDATE-b.exdate=min(a.AEDATE-b.exdate); ;
quit;

[Jagadishkatam]

Hi

I agree with your code and its generating the right output.

Also the below code works

proc sql;
select a.*,b.exdose
 from AE as a left join EX_ as b 
  on a.Subid=b.Subid and a.AEDATE-b.firstdosdt >0
    group by a.Subid,a.AEDATE 
     having b.firstdosdt=b.exdate and a.AEDATE-b.firstdosdt=min(a.AEDATE-b.firstdosdt) ;
quit;

[ChrisNZ]

When several alternatives are possible, you must consider the legibility (how easy is it to follow the intent and the logic) and performance.

[PGStats]

Sequential operations are better handled with a data step.  First, eliminate irrelevant records from EX (before drug is taken). Then interleave the two datasets. Finally, process the sequence of dates for each subId.

data EXtaken;
do until(last.subId);
    set ex; 
    by subId exdate; /* Optional, just checking */
    if not taken then taken = upcase(excat) = "STUDY DRUG FIRST TAKEN";
    if taken then output;
    end;
run;

proc sql;
create table seq as
select * from EXtaken
union all corr
select subId, aeDate as exDate, . as exDose from AE
order by subId, exDate;
quit;

data want;
do until(last.subId);
    set seq; by subId;
    if missing(exDose) then output;
    else lastDose = exDose;
    end;
keep subId exDate lastDose;
rename exDate=AEDATE;
run;

proc print; run;

[Ksharp]

How about this one :


data ex;
infile cards truncover;
input Subid        exdate  : date9.        exdose                    excat $100.;
format exdate  date9. ;
cards;
1              07FEB2016          5                        PSG night
1             17FEB2016         10                      Study drug first taken                    
1              27FEB2016        20
2             07JAN2016         20                        PSG night
2            17JAN2016          30                      Study drug first taken
2           17JAN2016           40
;
run;

data ae;
input Subid                AEDATE : date9.;
format  AEDATE date9.;
cards;
1                    18FEB2016
2                    11JAN2016
;
run;

proc sql;
select a.*,case when(sum(excat='Study drug first taken') ne 0) then exdose
 else . end as last_dose
 from ae as a left join ex as b
  on a.subid=b.subid and a.AEDATE ge b.exdate
   group by a.subid,a.AEDATE
    having b.exdate=max(b.exdate);
quit;

[ChrisNZ]

Like this?

data WANT;
  merge EX AE end=LASTOBS;      
  by SUBID ;
  if ^LASTOBS then set EX(keep=SUBID EXDATE rename=(SUBID=SUBID_NEXT EXDATE=EXDATE_NEXT) firstobs=2);
  if EXCAT='Study drug first taken' then READY+1;
  if READY                     %* we have found 'first taken' string       ;
   & ( EXDATE_NEXT >= AEDATE   %* and the next record is too late        ;
     | SUBID_NEXT ne SUBID     %*   (either date too large or new SUBID) ;
     | LASTOBS )               %*    or last observation)                ;
     then do;                  %* now is the time to write the record for this SUBID;
       if EXDATE < AEDATE then LASTDOSE=EXDOSE; 
       output;
       READY=0;
  end;
  keep SUBID AEDATE LASTDOSE;
run;

Subid	AEDATE	LASTDOSE
1	18FEB2016	10
2	11JAN2016	.