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

How to assign values by comparing dates in two data sets

knveraraju91 — Thu, 18 Aug 2016 21:15:37 GMT

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.

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

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;

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

Jagadishkatam — Fri, 19 Aug 2016 01:48:31 GMT

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.

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

PGStats — Fri, 19 Aug 2016 02:42:58 GMT

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;

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

knveraraju91 — Fri, 19 Aug 2016 03:04:35 GMT

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;

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

Ksharp — Fri, 19 Aug 2016 03:12:56 GMT

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;

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

ChrisNZ — Fri, 19 Aug 2016 03:32:22 GMT

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	.

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

Jagadishkatam — Fri, 19 Aug 2016 03:24:25 GMT

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;

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

ChrisNZ — Fri, 19 Aug 2016 03:31:14 GMT

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