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;
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;
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?
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.
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;
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;
@Ksharp Its always a pleasure to read your code.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.