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 | . |
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.