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

How to assign values by comparing the dates

knveraraju91 — Mon, 11 Jul 2016 21:25:34 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. Please help.

Subid exdate exdose
1 07FEB2016 5
1 17FEB2016 10
1 27FEB2016 20
2 07JAN2016 20
2 17JAN2016 30
2 17JAN2016 40

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

Re: How to assign values by comparing the dates

stat_sas — Mon, 11 Jul 2016 22:26:21 GMT

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;

Re: How to assign values by comparing the dates

ballardw — Mon, 11 Jul 2016 22:27:35 GMT

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.

Re: How to assign values by comparing the dates

Ksharp — Tue, 12 Jul 2016 01:07:08 GMT

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;

Re: How to assign values by comparing the dates

knveraraju91 — Tue, 12 Jul 2016 01:54:45 GMT

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

Re: How to assign values by comparing the dates

stat_sas — Tue, 12 Jul 2016 03:23:04 GMT

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;