try: proc transpose data=a out=kid prefix=kid; where relationship='Kid'; var age; by householdid familynumber; run;

data family(drop=relationship); merge a(where=(relationship='Mom')rename=(age=mom)) kid(drop=_name_); by householdid familynumber; run;

Dave Hapeman |-(:-) (515) 245-4765 hapemande@Wellmark.com

-----Original Message----- From: Dawn Owens-Nicholson [mailto:bluemoon@STAFF.UIUC.EDU] Sent: Friday, April 12, 2002 11:35 AM To: SAS-L@LISTSERV.UGA.EDU 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