Prev: jReview or PPD Patient Profiles proving difficult?
Next: Can't not start SAS
From: Al on 6 Aug 2010 11:20
Dear all :

This is the dataset i have

pat visit res
1 A 12
1 B 11
2 A 10
2 B 12
2 C 09
3 A 15
3 B 12
3 C 12

here are the rules for the output dataset

if a pat has just visit A and B then create a variable flag where flag
= average of res @ A and res @ B

if a pat has visits A , B and C then create a variable flag


Based on the 3 pairs of values that can determine flag, first
calculate the absolute difference for each pair and then form the
percentage change for
that pair (based on the absolute difference). The denominator used to
calculate the percentage difference is the earlier collected value of
the pair
(eg, for AB, the value of A serves as the denominator). The pair of
data used to determine the flag value is that pair with the smallest
percentage
difference. Calculate the mean value for this pair as the variable
flag


my solution, I have transposed the dataset and was able to get to the
ouptut by calculating % based on the above rules but not too thrilled
with the method .

Looking for a better solution

Thanks in advance
Al


From: Ya on 6 Aug 2010 12:02
On Aug 6, 8:20 am, Al <ali6...(a)gmail.com> wrote:
> Dear all :
>
> This is the dataset i have
>
> pat visit res
> 1      A    12
> 1      B    11
> 2      A    10
> 2      B    12
> 2      C    09
> 3      A    15
> 3      B    12
> 3      C    12
>
>   here are the rules for the output dataset
>
> if a pat has just visit A and B then create a variable flag where flag
> = average of res @ A and res @ B
>
> if a pat has  visits A , B and C then create a variable flag
>
> Based on the 3 pairs of values that can determine flag, first
> calculate the absolute difference for each pair and then form the
> percentage change for
> that pair (based on the absolute difference). The denominator used to
> calculate the percentage difference is the earlier collected value of
> the pair
> (eg, for AB, the value of A serves as the denominator). The pair of
> data used to determine the flag value is that pair with the smallest
> percentage
> difference. Calculate the mean value for this pair as the variable
> flag
>
> my solution, I have transposed the dataset and was able to get to the
> ouptut by calculating % based on the above rules but not too thrilled
> with the method .
>
> Looking for a better solution
>
> Thanks in advance
> Al

The following is almost there, you just need one more step:

data xx;
input pat visit $ res;
cards;
1 A 12
1 B 11
2 A 10
2 B 12
2 C 09
3 A 15
3 B 12
3 C 12
;

proc sql;
select a.pat,a.visit as avisit, a.res as ares,
b.visit as bvisit,b.res as bres,count(*) as npair,
abs(a.res-b.res)/a.res*100 as pct,
case when calculated pct=min(calculated pct) then 1 else 0 end as
pick
from xx a, xx b
where a.pat=b.pat and a.visit < b.visit
group by a.pat
;


pat avisit ares bvisit bres npair
pct pick
------------------------------------------------------------------------------
1 A 12 B 11 1
8.333333 1
2 A 20 B 10 3
50 0
2 A 20 C 10 3
50 0
2 B 10 C 10 3
0 1
3 B 12 C 12 3
0 1
3 A 15 B 12 3
20 0
3 A 15 C 12 3
20 0

HTH

Ya
 | 
Pages: 1
Prev: jReview or PPD Patient Profiles proving difficult?
Next: Can't not start SAS