DATA Step, Macro, Functions and more

How to assign values by comparing the dates

Reply
Super Contributor
Posts: 272

How to assign values by comparing the dates

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

 

 

Trusted Advisor
Posts: 1,204

Re: How to assign values by comparing the dates

[ Edited ]

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;

Super Contributor
Posts: 272

Re: How to assign values by comparing the dates

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

Trusted Advisor
Posts: 1,204

Re: How to assign values by comparing the dates

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;

Super User
Posts: 10,500

Re: How to assign values by comparing the dates

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.

Super User
Posts: 9,681

Re: How to assign values by comparing the dates

[ Edited ]
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;
Ask a Question
Discussion stats
  • 5 replies
  • 290 views
  • 3 likes
  • 4 in conversation