Date:         Fri, 19 Jun 2009 17:46:28 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: AE Derived Variable
Comments: To: Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
In-Reply-To:  <200906191539.n5JAq7AB032602@malibu.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Gerhard:

The elegance of any solution is in the eyes of the solver. As a rule SQLHeads don't add flags to datasets, only to displays or reports. This query yields results subject to the conditions:

proc sql;
   create table eSASSQLsolution as
   select * from test as r1
   where stdt >= dsdt
      OR exists (select 1 from test as r2
                 where r2.patno=r1.patno
                                   and r2.aeterm = r1.aeterm
                   and r2.aesev > r1.aesev
                                   and r2.stdt  > r1.stdt
                                 )
   ;
quit;

How this selection becomes part of a display with a "Yes" or "No" value in a column should depend, I think, on how one intends to use the display.
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Gerhard Hellriegel
Sent: Friday, June 19, 2009 11:39 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: AE Derived Variable

I think a one-pass solution is complicated if you need the YES in the
actual AND the previous obs. For that you must "prefetch" the next obs to
get it. More complicated, I think, if there are more than 2 aeterm
records. So for me the easy way (there is a elegant SQL solution for sure,
but that's not the easy way for me...):

data test;
  infile cards;
  input
  patno  aeterm:$20.     aesev  stdt: date9.     dsdt:date9. ;
cards;
1      Headache   1     12JUN08  15JUN08
1      fatigue    2     11JUN08  15JUN08
1      Headache   3     17JUN08  15JUN08
;
run;

proc sort data=test out=a;
  by patno aeterm aesev stdt;
run;

data b;
  set a;
  by patno aeterm aesev stdt;
  retain sev;
  if first.aeterm then do;
 sev=aesev;
  end;
  if last.aeterm and aesev>sev then output;
  keep patno aeterm;
run;

data result;
  merge a b(in=inb);
  by patno aeterm;
  length taae $5;
  taae = "NO";
  if inb then taae="YES";
run;

proc sort;
  by patno stdt;
  format stdt dsdt date9.;
run;

or whatever you want as order...

Gerhard





On Tue, 16 Jun 2009 13:27:41 -0700, Al <ali6058@GMAIL.COM> wrote:

>Hi All:
>
>I have an ae dataset in the following way
>
>patno  aeterm    aesev  stdt     dsdt      TEAE(Dervied)
>1      Headache   1     12JUN08  15JUN08   YES
>1      fatigue    2     11JUN08  15JUN08   NO
>1      Headache   3     17JUN08  15JUN08   YES
>
>I am supposed to create an derived variable TEAE
>where stdt >= dsdt or if the ae event's severity has increased
>after the dose was given .. example:First obs in the above
>example has ae occured prior to date of dose..but the same
>ae (Third obs) severity has increased from 1 to 3 in number.
>so i want 'YES' in column (TEAE --Derived Variable) for both
>first and third obs ..
>How can it be accomplished
>
>Thanks in advance