DATA Step, Macro, Functions and more

How to assign values by comparing dates

Reply
Super Contributor
Posts: 272

How to assign values by comparing dates

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;

 

 

Super Contributor
Posts: 266

Re: How to assign values by comparing dates

Posted in reply to knveraraju91

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;

Super User
Posts: 7,854

Re: How to assign values by comparing dates

Posted in reply to knveraraju91

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 272

Re: How to assign values by comparing dates

Posted in reply to KurtBremser

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.

Super User
Posts: 10,044

Re: How to assign values by comparing dates

Posted in reply to knveraraju91

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;


Super User
Posts: 10,044

Re: How to assign values by comparing dates

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



Super Contributor
Posts: 266

Re: How to assign values by comparing dates

@Ksharp Its always a pleasure to read your code.

Super Contributor
Posts: 272

Re: How to assign values by comparing dates

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  

Super User
Posts: 10,044

Re: How to assign values by comparing dates

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




Ask a Question
Discussion stats
  • 8 replies
  • 392 views
  • 5 likes
  • 4 in conversation