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;
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.
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;
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;
When several alternatives are possible, you must consider the legibility (how easy is it to follow the intent and the logic) and performance.
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;
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;
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 | . |
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.