Date:         Wed, 15 Apr 2009 16:57:33 -0400
Reply-To:     Søren Lassen <s.lassen@POST.TELE.DK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Søren Lassen <s.lassen@POST.TELE.DK>
Subject:      Re: Calculate Percentage Change
Comments: To: Siddharth Jain <sjain@AVEOPHARMA.COM>
Content-Type: text/plain; charset=ISO-8859-1

Something like this seems to work:

data have;
  length ID $3 visit response baseline 8;
  input ID visit response baseline;
cards;
001     1       100         100
001     2       120         100
001     3       80          100
001     4       87          100
001     5       100         100
002     1       102         100
002     2       122         100
002     3       82          100
002     4       89          100
002     5       102         100
002     6       82          100
;run;

proc sql;
create table want as select *,case
     when visit>=(select visit from
         (select id,min(visit) as visit from
             (select id,visit from have
                   group by id
                   having response=min(response)
                   )
              group by id
              ) as temp
            where have.ID=temp.ID
            ) then 100*(response-baseline)/baseline
    else .
    end from have;

It is a bit complicated because you have to make sure you get
the first visit no. that has the minimum response, hence the
double-nested subquery.

Regards,
Søren

On Wed, 15 Apr 2009 13:23:26 -0400, Siddharth Jain <sjain@AVEOPHARMA.COM>
wrote:

>Hello,
>
>I have a dataset like below:
>
>subjid visit response baseline_response
>001     1       100         100
>001     2       120         100
>001     3       80          100
>001     4       87          100
>001     5       100         100
>
>I need to calculate response percentage change from baseline using the
>formula per_change = (response - baseline_response)/ baseline_response *
>100. But I need to populate this field only for rows after the minimum
>response value. e.g.
>
>subjid visit response baseline_response per_change
>001     1       100    100                    .
>001     2       120    100                    .
>001     3       80     100                    -20
>001     4       87     100                   -13
>001     5       100    100                    0
>
>The per_change variable has to be populated only for the minimum response
>value (80) and values of response after that.
>
>
>Pls guide,
>Sid