Date:         Tue, 27 Nov 2007 13:54:36 -0500
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: aggregate or restructure data set
Comments: To: Peter Müller <mortal@GMX.LI>
Content-Type: text/plain; charset=ISO-8859-1

data have;
input ID Diag $;
cards;
1    A
1    b
1    c
2    b
2    c
3    A
3    b
3    f
3    g
4    g
4    y
;

proc sql;
select distinct diag,count(distinct id) as cnt
from (select * from have group by id having max(upcase(diag)='A'))
group by diag
;

 Diag           cnt
 ------------------
 A                2
 b                2
 c                1
 f                1
 g                1

On Tue, 27 Nov 2007 13:35:41 -0500, Peter M=?ISO-8859-1?Q?=C3=BCller?=
<mortal@GMX.LI> wrote:

>Dear list members,
>
>i'm new to SAS and have a question concerning the restructuring or
>aggregating of variables:
>
>I've two variables:
>The 1st is the ID and the 2nd are diagnostics for this patients.Everyone of
>those can have different diagnostics.
>I want to receive a dataset, that lists  all additional diagnostics, when a
>special diagnostic is true.
>
>ID  Diag
>1    A
>1    b
>1    c
>2    b
>2    c
>3    A
>3    b
>3    f
>3    g
>4    g
>4    y
>....
>
>I want to have those diagnostics, when A has been found. in this case
> for ID 1 and 3, so that the result looks like:
>
>Diag
>a  2
>b  2
>c  1
>f  1
>g  1
>
>Thank you,
>
>Peter Müller