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
Comments: To: Nat <venkateshnt@GMAIL.COM>
In-Reply-To:  A<1188325668.574686.101560@g4g2000hsf.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

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