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. Please help.
EX
Subid exdate exdose
1 07FEB2016 5
1 17FEB2016 10
1 27FEB2016 20
2 07JAN2016 20
2 17JAN2016 30
2 17JAN2016 40
AE
Subid AEDATE
1 18FEB2016
2 11JAN2016
Output needed.
Subid AEDATE Lastdose
1 18FEB2016 10
2 11JAN2016 20
For SUBID=1 10 is the last dose taken before AEdate
For SUBID=2 20 is the last dose taken before AEdate
Hi,
Please try this.
proc sql;
create table want as
select a.*, exdose as Lastdose from ae a
inner join ex b
on a.subid=b.subid
group by a.subid
having abs(exdate-aedate)=min(abs(exdate-aedate));
quit;
This code works, but I need the last dose and exdate less than aedate because that is the last dose taken before ae. Your code has ABS function which rounds to positive values and the exdate greater aedate also considered. PLease help
Hi,
I've modified the code. Please see below:
proc sql;
create table want as
select a.*, exdose as Lastdose from ae a
inner join ex b
on a.subid=b.subid
and exdate<aedate /* Restricting exdate before acdate */
group by a.subid
having aedate-exdate=min(aedate-exdate);
quit;
Proc SQL is very useful for joining on conditions. The following code should do what you want:
proc sql;
create table want as
select ae.subid, ae.aedate,ex.exdose as lastdose
from ae left join
(select subid, exdose
from ex
group by subid
having exdate=max(exdate))
;
quit;
You will get a warning about "remerging statistics back with original data" because of the Having clause.
If there is no dose associated with the SUBID then the Lastdose variable will be missing.
data EX;
input Subid exdate : date9. exdose;
format exdate date9.;
cards;
1 07FEB2016 5
1 17FEB2016 10
1 27FEB2016 20
2 07JAN2016 20
2 17JAN2016 30
2 17JAN2016 40
;
run;
data AE;
input Subid AEDATE : date9.;
format AEDATE date9.;
cards;
1 18FEB2016
2 11JAN2016
;
run;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.