Date: Tue, 28 Aug 2007 14:47:06 -0400
Reply-To: Jack Clark <JClark@CHPDM.UMBC.EDU>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Clark <JClark@CHPDM.UMBC.EDU>
Subject: Re: Transforming multiple records to one record
Content-Type: text/plain; charset="us-ascii"
You haven't provided rules about how to select a value for a variable
(within id) when more than one observation contains a non-missing value.
Your sample data shows all 4 observations for ID #101 with values for
the variable a5. What if they are not the same value? How should the
program determine which observation for ID #101 the value of a5 should
Sometimes it could be taken based on a date or sequence number on the
observations. Other times it is done by taking the MAX or MIN values,
which could be done with PROC SQL (example below).
create table need as
select id, max(a1) as a1, max(a2) as a2, max(a3) as a3, max(a4) as
max(a5) as a5, max(a6) as a6
group by id
Give us some more information about the logic rules.
Center for Health Program Development and Management
University of Maryland, Baltimore County
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Nat
Sent: Tuesday, August 28, 2007 2:28 PM
Subject: Transforming multiple records to one record
I have a dataset with multiple records for same ID like this:
input id a1 a2 a3 a4 a5 $ a6 $;
101 1 . . . *
101 . 2 . . *
101 . . 3 . *
101 . . . 4 *
102 1 . 3 . * .
102 . 2 . 4 .
I would like to have my output dataset to look like this: one record
id a1 a2 a3 a4 a5 a6
101 1 2 3 4 *
102 1 2 3 4 *
I tried to use both array and proc transpose to get the output, but
the variables are different data types (Character & Numeric), it gives
me an error.
Please input your suggestions. Thanks for your help.