topic Re: Assigning values by comparing dates in two datasets in SAS Programming

Assigning values by comparing dates in two datasets

knveraraju91 — Thu, 01 Dec 2016 03:07:13 GMT

Dear,

In my datasets the following values present. my code works for all obs except these obs. Please help thanks.

id exdate exdose

1 09MAY2016 50

1 09MAY2016 100

id aedate

1 09MAY2016

code;

proc sql;
create table dose as
select a.*,b.exdose
from ae as a left join EX as b
on a.id=b.id and a.Aedate-b.exdate >=0
group by a.id,a.aedate
having a.Aedate-b.exdate1=min(a.Aedate-b.exdate);
quit;

output getting ;

id aedate exdose

1 09MAY2016 50

output need;

id aedate exdose

1 09MAY2016 100

Re: Assigning values by comparing dates in two datasets

RahulG — Thu, 01 Dec 2016 04:04:39 GMT

Can you provide one liner how the data to be picked for the output. Is it maximum of of exdose or minimum of exdose Or is there any other logic. what condition are applied on dates?

Re: Assigning values by comparing dates in two datasets

knveraraju91 — Thu, 01 Dec 2016 04:51:19 GMT

Thanks for help.

I need to get the last dose taken on or before AE start date. My code worked for all obs except this obs because there are two doses taken on the day AE started. I need to get the maximum dose taken on that day. Thanks

Re: Assigning values by comparing dates in two datasets

mkeintz — Thu, 01 Dec 2016 07:28:03 GMT

You need a way to retain the maximum dose when there are multiple EX records on a date. In addition you have to retain the most recent EXDOSE in cas you encounter a date with an AE record but no EX record:

data want (rename=(date=aedate) drop=prior_dose);
  merge ae (rename=(aedate=date)) 
        ex (in=inex keep=id exdate exdose rename=(exdate=date));
  by id date;

  retain prior_dose;

  if first.id then prior_dose=.;
  if inex then do;
    if first.date then prior_dose=exdose;
    else exdose=max(exdose,prior_dose);
  end;
  else exdose=prior_dose;
  if inae;
run;

Re: Assigning values by comparing dates in two datasets

Ksharp — Thu, 01 Dec 2016 09:27:54 GMT

You have multiple obs for the same date and same id. Keep the last one you want.

proc sort data=ex;by id exdate;run;
data new_ex;
 set ex;
 by id exdate;
 if last.exdate;
run;

then PROC SQL.