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

How to assign values by comparing dates

knveraraju91 — Mon, 25 Jul 2016 03:43: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.

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

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

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;

Re: How to assign values by comparing dates

RahulG — Mon, 25 Jul 2016 04:55:25 GMT

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;

Re: How to assign values by comparing dates

Kurt_Bremser — Mon, 25 Jul 2016 06:26:48 GMT

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?

Re: How to assign values by comparing dates

Ksharp — Mon, 25 Jul 2016 06:56:39 GMT


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;

Re: How to assign values by comparing dates

Ksharp — Mon, 25 Jul 2016 07:05:33 GMT

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;

Re: How to assign values by comparing dates

RahulG — Mon, 25 Jul 2016 07:34:30 GMT

@Ksharp Its always a pleasure to read your code.

Re: How to assign values by comparing dates

knveraraju91 — Mon, 25 Jul 2016 18:20:56 GMT

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.

Re: How to assign values by comparing dates

knveraraju91 — Mon, 25 Jul 2016 18:39:32 GMT

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

Re: How to assign values by comparing dates

Ksharp — Tue, 26 Jul 2016 00:48:35 GMT

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;