Re: Transforming multiple records to one record

Nat,

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
come from?

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).

proc sql;
create table need as
select id, max(a1) as a1, max(a2) as a2, max(a3) as a3, max(a4) as
a4,
max(a5) as a5, max(a6) as a6
from sam1
group by id
;
quit;

Give us some more information about the logic rules.

Jack Clark
Research Analyst
Center for Health Program Development and Management
University of Maryland, Baltimore County


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Nat
Sent: Tuesday, August 28, 2007 2:28 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Transforming multiple records to one record

Hi everyone,

I have a dataset with multiple records for same ID like this:

data sam1;
input id a1 a2 a3 a4 a5 $ a6 $;
cards;
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
per id;

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.

Regards!
Nat