DATA Step, Macro, Functions and more

How to assign values by comparing dates in two data sets

Reply
Super Contributor
Posts: 272

How to assign values by comparing dates in two data sets

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; 

 

Trusted Advisor
Posts: 1,137

Re: How to assign values by comparing dates in two data sets

[ Edited ]
Posted in reply to knveraraju91

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.

Thanks,
Jag
Super Contributor
Posts: 272

Re: How to assign values by comparing dates in two data sets

Posted in reply to Jagadishkatam

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;

Trusted Advisor
Posts: 1,137

Re: How to assign values by comparing dates in two data sets

Posted in reply to knveraraju91

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;
Thanks,
Jag
PROC Star
Posts: 1,759

Re: How to assign values by comparing dates in two data sets

Posted in reply to Jagadishkatam

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

Respected Advisor
Posts: 4,920

Re: How to assign values by comparing dates in two data sets

Posted in reply to knveraraju91

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;
PG
Super User
Posts: 10,023

Re: How to assign values by comparing dates in two data sets

Posted in reply to knveraraju91
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;


PROC Star
Posts: 1,759

Re: How to assign values by comparing dates in two data sets

[ Edited ]
Posted in reply to knveraraju91

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 .
Ask a Question
Discussion stats
  • 7 replies
  • 346 views
  • 5 likes
  • 5 in conversation