Date:         Wed, 22 May 2002 10:35:43 -0700
Reply-To:     "Huang, Ya" <ya.huang@PFIZER.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <ya.huang@PFIZER.COM>
Subject:      Re: Calculating unique sum for multiple obs
Comments: To: "srbrown@POP.UKY.EDU" <srbrown@POP.UKY.EDU>
Content-Type: text/plain

proc sql is the way to go:

data xx;
input RECNO COMP;
cards;
1   0
2   1
2   1
2   2
3   0
3   2
3   3
4   5
4   5
4   5
5   0
5   1
;

options nocenter;
proc sql;
select distinct recno,
count(distinct case when comp ne 0 then comp else . end) as score
from xx
group by recno
;
----------
   RECNO     score
---------------------
       1         0
       2         2
       3         2
       4         1
       5         1

Kind regards,

Ya Huang

-----Original Message-----
From: Steve Browning [mailto:srbrown@POP.UKY.EDU]
Sent: Wednesday, May 22, 2002 10:10 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Calculating unique sum for multiple obs


>
>SAS programmers,
>
>         I have a data set of the following general form (below) with two
> variables RECNO (record number) and COMP (disease complications code).
 > From the multiple and varying number of observations, I want to create a
> dataset with a single unique observation (RECNO)and a new variable
> (SCORE) which is a cumulative total for every "unique" complication code.
> If the complication code is the same as any of the previous complications
> codes of the same RECNO, then I don't want it summed in the total. If the
> complication code is 0 then there are no complications.
>
>For example, if the dataset appears as below:
>
>Example:
>
>RECNO        COMP
>1               0
>2               1
>2               1
>2               2
>3               0
>3               2
>3               3
>4               5
>4               5
>4               5
>5               0
>5               1
>
>Then I would want a dataset that looks like:
>
>RECNO                   SCORE
>1                       0
>2                       2
>3                       2
>4                       1
>5                       1
>
>Can you please provide a bit of outline code that would yield this result?
>Your help is greatly appreciated.

         Steve Browning
          Assistant Professor of Epidemiology
         College of Nursing and the
         Department of Preventive Medicine and   Environmental Health
         549 CON/HSLC Bldg
         Phone: 859-323-6354
         FAX: 859-323-1038
         e-Mail: srbrown@pop.uky.edu
         Website: http://www.mc.uky.edu/scahip and
                 http://www.mc.uky.edu/Nursing/bios/

"Doing more things faster is no substitute for doing the right things"