Transforming multiple records to one record

Transforming multiple records to one record

Post by Nat » Thu, 30 Aug 2007 03:27:48


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
 
 
 

Transforming multiple records to one record

Post by tobydun » Thu, 30 Aug 2007 03:35:21

Untested but try:

Data Need ;
UpDate Sam1 ( Obs = 0 )
Sam1 ;
By ID ;
Run ;





Toby Dunn


Two wrongs are only the beginning.

Success always occurs in private and failure in full view.

Experience is something you don't get until just after you need it.





From: Nat < XXXX@XXXXX.COM >
Reply-To: Nat < XXXX@XXXXX.COM >
To: XXXX@XXXXX.COM
Subject: Transforming multiple records to one record
Date: Tue, 28 Aug 2007 18:27:48 -0000

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

_________________________________________________________________
A new home for Mom, no cleanup required. All starts here.
http://www.yqcomputer.com/ ?ocid=TXT_TAGHM&loc=us

 
 
 

Transforming multiple records to one record

Post by JClar » Thu, 30 Aug 2007 03:47:06

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: XXXX@XXXXX.COM ] On Behalf Of Nat
Sent: Tuesday, August 28, 2007 2:28 PM
To: XXXX@XXXXX.COM
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
 
 
 

Transforming multiple records to one record

Post by mlhowar » Thu, 30 Aug 2007 04:11:07

Nat,

Is there the same number of lines per ID? If so, you might be able just =
to specify the number of lines and read the appropriate variable from =
each line, like this below (code below is not tested).

ata sam1;
infile cards missover n=3D3;
input=20
#1 @1 id $3. @5 a1 $1.
#2 @7 a2 $1.
#3 @9 a3 $1.;
cards;
101 1 . . . *=20
101 . 2 . . *=20
101 . . 3 . *=20
;
run;

proc print;
run;

-Mary
----- Original Message -----=20
From: Nat=20
To: XXXX@XXXXX.COM =20
Sent: Tuesday, August 28, 2007 1:27 PM
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 . . . *=20
101 . 2 . . *=20
101 . . 3 . *=20
101 . . . 4 *=20
102 1 . 3 . * .
102 . 2 . 4 .=20
;

I would like to have my output dataset to look like this: one record
per id;

id a1 a2 a3 a4 a5 a6
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
101 1 2 3 4 * =20
102 1 2 3 4 * =20

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
 
 
 

Transforming multiple records to one record

Post by Perr » Thu, 30 Aug 2007 04:11:59

Hi Nat,

Try this, with one caveat I'll state afterward...

/* Create empty result table with desired structure */

proc sql;
create table sam1
(id numeric,
a1 numeric,
a2 numeric,
a3 numeric,
a4 numeric,
a5 character(1),
a6 character(1)
);
quit;

/* Read dataset containing multiple records per id */

data sam_trans;
infile cards truncover;
input id a1 a2 a3 a4 a5 $ a6 $;
cards;
101 1 . . . * +
101 . 2 . . * +
101 . . 3 . * +
101 . . . 4 * +
102 1 . 3 . * .
102 . 2 . 4 . +
;
run;

/* Sort here by id if necessary */

/* Use the dataset with multiple records per id as a transaction
dataset, and update the final dataset that you want */

data sam1;
update sam1 sam_trans;
by id;
run;

Now, this (along with any other solution) will probably break down if
you have multiple (different) non-missing values for a variable within
an id group. You should probably consider how to handle this situation
should it arise.

HTH,

Perry




> 101 1 . . . * >> 101 . 2 . . * gt; > 101 . . 3 . * >
> 101 . . . 4 * gt; > 102 1 . 3 . *>.
> 102 . 2 . 4 . >€ > ;> >
> I would like to have my output dataset to look like this: one rec>rd
> per >d;> >
> id a1 a2 a3 a4 a5 gt;a6
> ================>==
> 101 1 2 3 4 * gt; > 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 >ives
> me an e>ro>.
>
> Please input your suggestions. Thanks for your >el>.
>
> Reg>rds!
> Nat
 
 
 

Transforming multiple records to one record

Post by Nat » Thu, 30 Aug 2007 04:19:10


Thanks a lot Tobby, Jack & Perry for your suggestions. Jack and Perry,
you guys exactly pointed out the problems arising if the dataset has
more than 1 non-missing value for a record. We have to have a logic
for this like as Jack said Maximum of 2 non-missing values or minimum
of 2 non-missing values. I appreciate all of your timely help.
 
 
 

Transforming multiple records to one record

Post by jim4sta » Thu, 30 Aug 2007 18:42:05

Hi Nat,

My macro MPR2SR just does that and more in a quite flexible way.
Basically it uses an UPDATE statement in a data step. See:
http://www.yqcomputer.com/

Regards - Jim.



>101 1 . . . * >>101 . 2 . . * gt; >101 . . 3 . * >
>101 . . . 4 * gt; >102 1 . 3 . *>.
>102 . 2 . 4 . > >>;> >
>I would like to have my output dataset to look like this: one rec>rd
>per >d;> >
> id a1 a2 a3 a4 a5 >a6
>================>==
>101 1 2 3 4 * > >102 1 2 3 4 * > gt; >
>I tried to use both array and proc transpose to get the output> but
>the variables are different data types (Character & Numeric), it >ives
>me an e>ro>.
>
>Please input your suggestions. Thanks for your >el>.
>
>Reg>rds!