Date:         Fri, 12 Apr 2002 13:22:17 -0400
Reply-To:     diskin.dennis@KENDLE.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         diskin.dennis@KENDLE.COM
Subject:      Re: Data re-shaping question
Content-type: text/plain; charset=us-ascii

Dawn,

I think this will do it (untested).
You could also use Proc Trannspose.

Assuming A is sorted on HouseholdID and FamilyNumber

Data B(drop=Relationship Age i);
merge a (where=(Relationship eq  'Mom') rename=Age=MomAge)
             a (where=(Relationship eq  'Kid') )
             ;
by HouseholdID FamilyNumber;

array  KidAge(3);
retain KidAge1-KidAge3;

/* Note - if you ever have more than 3 Kids this will error out - on
purpose - you could put message logic */
i+1;
KidAge(i) =Age;
if last FamilyNumber;
output;
do i = 1 to dim(KidAge);
   KidAge(i) = .;
end;
i=0;

HTH,
Dennis Diskin




From:  Dawn Owens-Nicholson <bluemoon@STAFF.UIUC.EDU>@LISTSERV.UGA.EDU> on
       04/12/2002 12:35 PM

Please respond to bluemoon@uiuc.edu

Sent by:    "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>


To:    SAS-L@LISTSERV.UGA.EDU
cc:

Subject:    Data re-shaping question


Hi All,

I have a data re-shaping question which I need help with.  I hope
one of you has an idea for me...

Here is the data I have:

data a;
input HouseholdID FamilyNumber Relationship $ Age;
datalines;
1 1 Dad 31
1 1 Mom 30
1 1 Kid 10
1 1 Kid  9
1 2 Mom 20
1 2 Kid  1
2 1 Dad 35
2 1 Mom 35
3 1 Mom 20
3 1 Kid  4
3 1 Kid  3
3 1 Kid  1
4 1 Kid 17
4 1 Kid  2
;
run;

Here is what I want it to look like:

Household  Family
    ID     Number  MomAge  KidAge1  KidAge2  KidAge3
     1       1       30       10        9        .
     1       2       20        1        .        .
     2       1       35        .        .        .
     3       1       20        4        3        1
     4       1        .       17        2        .

So, the issues are:
 1. Ignore the Dad records
 2. There are different numbers of Kid records
 3. There might not be a Mom record


The program I wrote which does not work is:


data b (drop = i Relationship Age);
array Ages{4} MomAge KidAge1-KidAge3;
do i = 1 to 4;
set a; by HouseholdID FamilyNumber;
Ages{i} = Age;
if last.FamilyNumber then return;
end;
run;


Can you help?

Thanks in advance,
Dawn